Question on summarizing data/creating table

[odonovanc]

I have a large set of data and I am going to provide a summary of it here. It looks something like this starting in A1:

President	Phone Number	Location	Date	Start Time
George Washington	(123)456-7899	White House	June 21, 2021​	07:00 AM
George Washington	(123)456-7899	White House	June 22, 2021​	07:00 PM
George Washington	(123)456-7899	White House	June 24, 2021​	07:00 AM
George Washington	(123)456-7899	Pentagon	June 26, 2021​	07:00 PM
George Washington	(123)456-7899	White House	June 27, 2021​	07:00 AM
John Adams	(555) 555-5555	Capitol Building	June 25, 2021​	07:00 PM
John Adams	(555) 555-5555	Capitol Building	June 26, 2021​	07:00 AM
John Adams	(555) 555-5555	Capitol Building	June 28, 2021​	07:00 PM

I need to summarize these presidents and the location in a format that looks like this:

Phone Number	President	Location	Date 1	Time 1	Date 2	Time 2	Date 3	Time 3	Date 4	Time 4
(123)456-7899	George Washington	White House	June 21, 2021​	June 24, 2021​	07:00 AM​	07:00 PM​	June 24, 2021​	07:00 AM​	June 27, 2021​	07:00 AM​
(123)456-7899	George Washington	Pentagon	June 26, 2021​	07:00 PM​
(555) 555-5555	John Adams	Capitol Building	June 25, 2021​	07:00 PM​	June 26, 2021​	07:00 AM​	June 28, 2021​	07:00 PM​

So each president and the corresponding location and then the first time they appear there would be date 1, the second time date 2, etc, and corresponding times. Ideally the only blank dates would be at the end if they do not show up. This is a weekly report, so there are never more than 7 dates. Anyone have ideas?

I tried pivoting to summarize, but there are too many columns. Let's assume the bottom set of results, the column phone number is cell A11. If phone number cannot come first, I can move it later. The president's phone numbers will always be the same and unique for each president.

 

[severynm]

So this was an interesting one to solve. Because of your need to pivot/unpivot the data in your columns, I immediately thought of Power Query. However, it's a little more complex then that because we also have to increment the dates/times for each instance of each president. I ended up sharing a little code from here and ended up with the following. It's probably definitely not the most elegant, but it should work. You can clean up things as you like.

If you have your data in an excel table, start a new query from that table and replace what is in the advanced query with this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], //Replace with your table name where the data is coming from
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"President", type text}, {"Phone Number", type text}, {"Location", type text}, {"Date", type text}, {"Start Time", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"President", Order.Ascending}, {"Location", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    GroupedRows = Table.Group(#"Added Index", {"President", "Location"}, {{"tmp", each _, type table [President=nullable text, Phone Number=nullable text, Location=nullable text, Date=nullable text, Start Time=nullable number, Index=number]}}),
    #"AddedCustom" = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([tmp],"Occurrence", 1,1)),
    #"RemovedOtherColumns" = Table.SelectColumns(AddedCustom,{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(RemovedOtherColumns, "Custom", {"President", "Phone Number", "Location", "Date", "Start Time", "Index", "Occurrence"}, {"President", "Phone Number", "Location", "Date", "Start Time", "Index", "Occurrence"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"President", "Phone Number", "Location", "Index", "Occurrence"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Attribute Numbered", each [Attribute] & " " & Text.From([Occurrence])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Occurrence", "Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Attribute Numbered"]), "Attribute Numbered", "Value")
in
    #"Pivoted Column"

That gets me this table:

You said you might end up with more columns, so run this on your full data source, then you can adjust column types and date/time formatting as appropiate. It's also trivially easy to rearrange the columns in Power Query - simply drag them to where you want them.