Moving duplicate date data to another column

[AprilLynnRN]

Hello everyone, I've been using AI and searches to no avail. I want to create an "Event" Table for all intents and purposes so that it can go into my Dynamic Calendar. This is the shortened version of a Table I am working with:

Dates	Caregiver	Preceptor	Orientation Week	Helper	Event
6/1/2026	Melanie	Angel	Week 2	1	Melanie / Angel / Week 2
6/2/2026	Melanie	Angel	Week 2	1	Melanie / Angel / Week 2
6/2/2026	Tyler	Aylin	Week 1	2	Tyler / Aylin / Week 1
6/3/2026	Tyler	Aylin	Week 1	1	Tyler / Aylin / Week 1
6/4/2026	Tyler	Aylin	Week 1	1	Tyler / Aylin / Week 1
6/6/2026	Melanie	Angel	Week 3	1	Melanie / Angel / Week 3
6/7/2026	Melanie	Angel	Week 3	1	Melanie / Angel / Week 3
6/10/2026	Tyler	Aylin	Week 2	1	Tyler / Aylin / Week 2
6/11/2026	Tyler	Aylin	Week 2	1	Tyler / Aylin / Week 2
6/12/2026	Tyler	Aylin	Week 2	1	Tyler / Aylin / Week 2
6/12/2026	Melanie	Angel	Week 4	2	Melanie / Angel / Week 4

I would like to use this table to create a new table that has one date per row with multiple event columns so that it looks like this:

Date	Caregiver/Preceptor/Orientation Week	Caregiver/Preceptor/Orientation Week
6/1/2026	Melanie / Angel / Week 2
6/2/2026	Melanie / Angel / Week 2	Tyler / Aylin / Week 1

I feel like it can't be that hard but I can't seem to get the info I need to move this data. When I've used Power Query the data ends up looking odd and I'd rather not paste formulas in there. I need something that I can paste the dates and event list to a table. I hope this makes sense.

The purpose of this is to take the built schedule for my orientees and paste it into the table, sort it by date, then move the data to a new table for the dynamic calendar. Maybe I am doing it wrong but I'm open to changing my original process.

My orientees schedule looks like this:

Caregiver	Orientation Dates	Preceptor
Melanie	5/15/2026	Gina
Hire Date	5/16/2026	Gina
5/4/2026	5/17/2026	Christy
	5/22/2026	Angel
	5/23/2026	Angel
	5/29/2026	Gina
	5/30/2026	Gina
	6/1/2026	Angel
	6/2/2026	Angel
	6/19/2026	Angel
	6/20/2026	Angel

I am using Excel 365. I've tried doing pivot tables also but most searches aren't really answering my questions. I finally got what I needed from a power query after more than an hour of AI answers. When I tried to do it again I could not duplicate it. I am hoping for a simpler solution.

I hope someone can help. Thanks.

 

[shamimarita]

Hello AprilLynnRN,

Since you're using Excel 365, I think you're actually very close to a formula-only solution and may not need Power Query at all.

If your source data is in an Excel Table named ScheduleTbl with columns Dates and Event, you can first create a list of unique dates:

=SORT(UNIQUE(ScheduleTbl[Dates]))

Then, in the first Event column beside each date, use:

=TRANSPOSE(FILTER(ScheduleTbl[Event],ScheduleTbl[Dates]=A2,""))

• where A2 contains the date from the unique date list.

The FILTER function returns all events for that date, and TRANSPOSE spills them horizontally across the row, producing a result similar to:

Date Event 1 Event 2
6/1/2026 Melanie / Angel / Week 2
6/2/2026 Melanie / Angel / Week 2 Tyler / Aylin / Week 1

One advantage of this approach is that when you paste additional schedule data into the source table, the unique-date list and event columns update automatically.

For a dynamic calendar, another option is to skip creating the intermediate table entirely and have the calendar pull events directly from the source table with FILTER(). That often makes maintenance much easier because you only need to maintain one table.

 

[AprilLynnRN]

You are my hero! Where have you been all my life? Thank so much, this did work. I knew there had to be an easier way.

The only issue I am having now is I am getting spill error when trying to put that data into a Table. I keep getting spill errors, but if I copy and paste into a plain cell, it works. Riddle me that.

 

[AprilLynnRN]

I copied the data values instead of the formula and it worked. I would love to just be able to update that one original table and put the data in another table but the battle continues.