Turn Continues Vertical Values Into One Row With Min & Max Values

[excelakos]

Hello community!!!

We are trying to solve the below problem.
Our system can export data like below

Hotel Tourism Destination Code	Hotel Name	Hotel Code - TK	Contracting Group	Room Code	Room Description	Date
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	04/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	21/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	22/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	23/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	24/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	25/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	26/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	27/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	28/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	29/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	30/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	04/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	21/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	22/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	23/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	24/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	25/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	26/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	27/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	28/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	29/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	30/05/2024

What we need to build from the above table is to keep only one row per hotel, room code with min & max dates.
So for example for the first 2 rows we need to keep only one like the below

Hotel Tourism Destination Code	Hotel Name	Hotel Code - TK	Contracting Group	Room Code	Room Description	Date From	Date Till
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	04/05/2024	05/05/2024​
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	21/05/2024​	30/05/2024​
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	04/05/2024	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	21/05/2024​	30/05/2024​

We would be very satisfied if we could get a final result like the below, so we could filter the non blank cell in last column (Date Till) and extract what we need.

Always keeping in mind that columns

Hotel Code - TK
Contracting Group
Room Code

must be accumulated into the procedure

Hotel Tourism Destination Code	Hotel Name	Hotel Code - TK	Contracting Group	Room Code	Travel Kiss Room Description	Date	Date Till
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	04/05/2024	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	21/05/2024	30/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	22/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	23/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	24/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	25/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	26/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	27/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	28/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	29/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00GV00	Standard Bungalow (GardenView)	30/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	04/05/2024	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	05/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	21/05/2024	30/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	22/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	23/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	24/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	25/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	26/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	27/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	28/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	29/05/2024
Dest	Hotel name	AMTSGR0MGK	OWN2	BUSD00SV00	Standard Bungalow (SeaView)	30/05/2024

I hope its clear enough for you to understand the needed result.

Thank you in advance

 

[hagia_sofia]

It seems clear what is the intended outcome however based on this data it is impossible to identify "Date Till", i.e. how can you tell that first date is 05/05/2024 and second 30/05/2024? Is it not there is something missing?

 

[excelakos]

It seems clear what is the intended outcome however based on this data it is impossible to identify "Date Till", i.e. how can you tell that first date is 05/05/2024 and second 30/05/2024? Is it not there is something missing?

Geia sou hagia_sofia

We must read the consecutive dates (always by hotel code, room code). So for the first example we start with 4/5/2024. The below row is again for the same criteria and it reads 5/5/2024. The 3rd row is for the same criteria but it is not 6/5/2024, it is 21/5/2024. So the logic stops there and then go to the row with 4/5/2024 and then we add date till as the 5/5/2024 cause we never had the 6/5/2024.

Now the logic starts again on the row with 21/5/2024. It must read all the below consecutive dates to recognize that it goes till 30/5/2024. The next row is not consecutive to 30/5/2024 and also the criteria have changed..Different room code.

So in the row with 21/5/2024 we must get the 30/5/2024 as the date till.

Hope this answers your question