Group by Column Question

[Robert_4316]

Good Afternoon,

I have a quick question. I have a data set containing hotel stay information across two hotel in tabular format. I'd like to group the stay info by guest name and have the sets of stay data added as new columns like so:

Current:

Guest Name	Property	Arrival	Departure	Room Type
John Doe	KOE	1/20/2021	1/28/2021	DK
John Doe	MAN	1/28/2021	2/2/2021	PK
Bob Ross	KOE	2/9/2022	2/11/2022	DXSK
Bob Ross	MAN	2/11/2022	2/13/2022	SUK

Desired:

Guest Name	Property1	Arrival1	Departure1	Room Type1	Property2	Arrival2	Departure2	Room Type2
John Doe	KOE	1/20/2021	1/28/2021	DK	MAN	1/28/2021	2/2/2021	PK
Bob Ross	KOE	2/9/2022	2/11/2022	DXSK	MAN	2/11/2022	2/13/2022	SUK

Some considerations: if the guest stayed at a 3rd property, it would create a third set of columns for the 3rd entry, then the fourth, etc.

Would anyone be able to point me in the right direction?

Thank you!

Robert

[#2 · Accepted solution]

@Robert_4316 Perhaps the attached PowerQuery solution helps you on your way. I have deliberately left all intermediate steps visible, but the can be condensed, if you want. And a real PQ master will probably come-up with a better approach.

Edit: Came up with a slightly shorter version and alternative version (attachment  replaced)

[#3]

Hi Riny_van_Eekelen,

Thank you so much; this works perfect. I really appreciate the help!

Sincerely,

Robert

[#4]

@Robert_4316 Glad I could help. Learning myself from it every day.

[#5]

@Robert_4316 

Let me offer yet another alternative. I was uncomfortable with your (potentially) endless set of columns, given how frequently some folks travel. Depending on how you use the output here, if you're just checking on one individual at a time, a simple FILTER formula can give you endless rows very easily, and by adding the SORT function, you can get them in date order (just ensure that the dates in your data set are stored as Excel dates)

Here's what it might look like for Bob Ross's travel--I've added a row for him, and added Frank Sinatra in for good measure as well.

Here's the formula in cell I5 of the attached sheet. That's the only place where the formula is entered; it is a "dynamic array" function and fills all the adjacent cells so long as the criterion is met (the name = name in cell I2) 

=SORT(FILTER(Table1,Table1[Guest Name]=I2),3,1,0)

[#6]

@Robert_4316 

I agree with @mathetes , but that all depends on goals.

Here is to play with modern Excel functions to generate array like this

Here it is

guestStays=
LAMBDA( 
LET(
    nameList, Table1[Guest Name]
    , headers, Table1[#Headers]
    , names, ROWS( nameList)
    , guestNames, UNIQUE(nameList)
    , guests, ROWS( guestNames )
    , maxStays, MAX( SCAN(0, guestNames, LAMBDA(a,v, SUM(--(v=nameList) ) ) ) )
    , dataColumns, COLUMNS(Table1) - 1
    , dataHeaders, TAKE( headers,, -dataColumns)

    , combineName,
        LAMBDA( name,
            DROP(
                REDUCE(1, SEQUENCE( names ),
                    LAMBDA( a, v,
                        IF( INDEX( INDEX( nameList, v ) = name, 1, 1),
                            HSTACK(a, DROP( CHOOSEROWS( Table1, v),,1) ),
                            a) 
                    )
            ),, 1)
        )

    , fakeArray, LAMBDA( array, LAMBDA( array))

    , arrayOfDataArrays,
        MAP( guestNames, LAMBDA( name, fakeArray( combineName(name) ) ) )
    , stackDataArrays,
        REDUCE(, arrayOfDataArrays,
            LAMBDA( a, v, LAMBDA( VSTACK( a(), v() ) ) ) )()
    , cleanArray, IFNA(stackDataArrays, "")

    , arrayOfHeaders,
        MAP( SEQUENCE(maxStays), LAMBDA( n, fakeArray(dataHeaders)) )
    , expandHeaders,
        REDUCE(, arrayOfHeaders,
            LAMBDA( a, v, LAMBDA( HSTACK( a(), v() ) ) ) )()

    , HSTACK( 
        VSTACK( TAKE(headers,, 1 ), guestNames ),
        VSTACK( expandHeaders, cleanArray ) 
    )
))