Worksheet that summarizes travel schedule date/locations found on another worksheet?

[alexcr]

Let's say there's a worksheet with 31 columns, with the first row of this worksheet containing the dates for the month of August, i.e. 8/1-8/31, and the second row containing a travel location for each of those dates.

For example, let's say the columns for 8/1-8/8 contain "New York", the columns for 8/9-8/16 contain "London", the columns for 8/17-8/24 contain "New York" again, and the columns for 8/25-8/31 contain "Madrid".

What formulas would you use to create a second worksheet that summarizes each trip found in the first worksheet? Specifically, for the example illustrated above, the second worksheet would automatically generate four columns, i.e. one column for each trip, with the first row showing "8/1-8/8", "8/9-8/16", "8/17-8/24", "8/25-8/31" and the row beneath this showing "New York", "London", "New York", "Madrid".

The key thing is that this summary worksheet would need to automatically update, so if a fifth or sixth trip were added, additional columns would automatically appear to reflect this.

To add another layer of complexity to this, let's say that the first worksheet also contains a row, or perhaps multiple rows, with costs for each day at each location. How would you also show the sum of the costs for each trip in the summary worksheet?

 

[KRice]

Here is one approach. The beginning of the month for creating the daily calendar is a user input in A1. The user populates B5:AF8 with trip location and costs associated with each day. A helper row in B3:AF3 builds and index representing contiguous blocks of matching locations, which is used to create the summary table. You can hide this row or turn the text white to match the background color.

The summary table is shown on the same sheet for convenience. The first row is another helper row of all of the trip indexes in the source table where the "locations" are not blank. Then three formulas are used to create the date range for each trip, the location, and the total cost.
The source table...I'm not sure why XL2BB displays one formula here the way it does, but the formula in B3 and copied across to the right is:

=IF(A5=B5,MAX($A3:A3),N(A3)+1)

MrExcel_20220721.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	8/1/2022
2
3	Label	1	1	1	1	1	1	2	2	3	3	3	3	3	3	3	3	4	4	5	5	5	5	6	7	8	8	8	8	9	9
4	Date	8/1	8/2	8/3	8/4	8/5	8/6	8/7	8/8	8/9	8/10	8/11	8/12	8/13	8/14	8/15	8/16	8/17	8/18	8/19	8/20	8/21	8/22	8/23	8/24	8/25	8/26	8/27	8/28	8/29	8/30
5	Location	New York	New York	New York	New York	New York	New York			London	London	London	London	London	London	London	London			New York	New York	New York	New York	Chicago	New York	Madrid	Madrid	Madrid	Madrid
6	Cost1	2	6	4												100				1				5		10		50
7	Cost2	7	3	5											200						2		2	6		10		50
8	Cost3	3	2	1										300								3		7	5.5	10		50
DailyTripLog

Cell Formulas
Range	Formula
B3:AE3	C3	=IF(B5=C5,MAX($A3:B3),N(B3)+1)
B4:AF4	B4	=SEQUENCE(,DAY(EOMONTH(A1,0)),A1)
Dynamic array formulas.

...and the summary table:

MrExcel_20220721.xlsx
	AH	AI	AJ	AK	AL	AM	AN
3	Label	1	3	5	6	7	8
4	Dates	8/1-8/6	8/9-8/16	8/19-8/22	8/23-8/23	8/24-8/24	8/25-8/28
5	Location	New York	London	New York	Chicago	New York	Madrid
6	Total Cost	33.00	600.00	8.00	18.00	5.50	180.00
DailyTripLog

Cell Formulas
Range	Formula
AI3:AN3	AI3	=UNIQUE(FILTER(B3:AF3,B5:AF5<>""),1)
AI4:AN4	AI4	=TEXTJOIN("-",,TEXT(XLOOKUP(AI3,$B$3:INDEX($B$3:$AF$3,COUNT($B4#)),$B4#,,0,1),"m/d"),TEXT(XLOOKUP(AI3,$B$3:INDEX($B$3:$AF$3,COUNT($B4#)),$B4#,,0,-1),"m/d"))
AI5:AN5	AI5	=INDEX($B$5:INDEX($B$5:$AF$5,COUNT($B4#)),MATCH(AI3,$B$3:INDEX($B$3:$AF$3,COUNT($B4#)),0))
AI6:AN6	AI6	=SUM(FILTER($B$6:$AF$8,$B$3:$AF$3=AI$3))
Dynamic array formulas.

 

[alexcr]

Thank you, @KRice! This got it done for me. I really appreciate it.

BTW, I hadn't even thought about the possibility of there being days without a location, so nice work capturing that case, too.

 

[KRice]

You're welcome...I'm happy to help.