SOLVED

Group by Column Question

New Contributor

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 NamePropertyArrivalDepartureRoom Type
John DoeKOE1/20/20211/28/2021DK
John DoeMAN1/28/20212/2/2021PK
Bob RossKOE2/9/20222/11/2022DXSK
Bob RossMAN2/11/20222/13/2022SUK

 

Desired:

 

Guest NameProperty1Arrival1Departure1Room Type1Property2Arrival2Departure2Room Type2
John DoeKOE1/20/20211/28/2021DKMAN1/28/20212/2/2021PK
Bob RossKOE2/9/20222/11/2022DXSKMAN2/11/20222/13/2022SUK

 

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

 

5 Replies
best response confirmed by Robert_4316 (New Contributor)
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)

Hi Riny_van_Eekelen,

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

Sincerely,

Robert

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

 

@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.

mathetes_0-1650124058436.png

 

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)

@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

image.png

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 ) 
    )
))