Looking for automated way to build an event attendance list

[Randy340]

I need to create attendance lists for events where several hundred tickets are sold.  Some people buy a single ticket while others buy multiple tickets.  Is there a formula I can use so that (in this example) the first table becomes the second table:

Last Name	Name	No. seats	Seats
Goplik	Bert	1	L15
Goplik	Bert	1	L16
Heep	Judith	1	C16
Heep	Judith	1	C17
Park	Mary	1	E6
Booth	Peter	1	K10
Booth	Peter	1	K11
Holmes	Sally	1	G19
Last Name	Name	No. seats	Seats
Goplik	Bert	2	L15 L16
Heep	Judith	2	C16 C17
Park	Mary	1	E6
Booth	Peter	2	K10 K11
Holmes	Sally	1	G19

[#2]

@Randy340  you could use a pivot table or power query to do this or using modern formulas you can use:

=LET(
    in, A2:D9,
    names, UNIQUE(TAKE(in, , 2)),
    seats, BYROW(
        names,
        LAMBDA(r,
            LET(
                tix, FILTER(in, MMULT(--(TAKE(in, , 2) = r), {1; 1}), ""),
                SUM(tix) & ", " & TEXTJOIN(" ", , TAKE(tix, , -1))
            )
        )
    ),
    HSTACK(names, TEXTBEFORE(seats, ","), TEXTAFTER(seats, ", "))
)

[#3]

@Randy340 

In case you are looking for a Power Query solution (very easy to follow) use the code below:

let
    Source = Excel.CurrentWorkbook(){[Name="tblRaw"]}[Content],
    #"Group rows" = Table.Group(Source, {"Last Name", " Name"}, {{"No. Seats", each List.Sum([No. seats]), type number}, {"Seats", each Text.Combine([Seats],"#(lf)")}})
in
    #"Group rows"

Just copy and paste the code into the PQ advanced editor. I named the source table tblRaw. Adjust the name for your use case and load the data back to Excel. All done! 

[#4]

@Randy340 Here is one more way of utilizing the modern excel functions like GROUPBY() although one needs to use MS365 with the Beta Channel (Office Insiders) enabled. Personally, I would have gone using POWER QUERY which makes it way easier, simpler and more importantly time saving, effortless as well can handle quite a lot of data as far I know. Perhaps here is my attempt using the said function.

=LET(
     _GroupByLastName_Name, GROUPBY(A2:B9,HSTACK(C2:D9),HSTACK(SUM,ARRAYTOTEXT),0,0),
     _DroppingTempHeader, DROP(_GroupByLastName_Name,1),
     _ReplacingCommasWithLineFeeds, SUBSTITUTE(_DroppingTempHeader,", ",CHAR(10)),
     VSTACK(A1:D1,_ReplacingCommasWithLineFeeds))

Note: Variable _ReplacingCommasWithLineFeeds is unnecessary here, since the required output for Seats column uses line feeds to join them hence added to substitute the commas with line feeds, but if you are satisfied with the use commas, then can remove that part.

[#5]

Hi Maciej,
Your solution looks perfect for what I need. I am a tech idiot so I am not sure when I open my source table in Excel how I get to the PQ advanced editor in Excel and how/where I enter the code you provided. I don't know if my version of Excel could be the problem. It is Microsoft Excel 2016 MSO (Version 2401 Build 16.0.17231.20194) 32 bit. When I go into may account it says it is up to date. Thanks so much for your time.

[#6]

Hi @Randy340 ,

Follow the process to apply PQ:

• GoTo Data Tab ,
• Under Get & Transform Data, Click on Get Data , 
• On doing above, will give you some options, click on the 5th option(From Other Sources), which opens few more options --> Click on Blank Query,
• On doing the step above opens the Power Query Window. From there under Home Ribbon Tab, Click on Advanced Editor,
• Now, whatever you see in the editor completely delete and paste the m-code given by @Maciej_Kopczynski and then click on Done, while doing all of these ensure to either have same table name as given in the code or just change the table name as per your data in the first line of the code, also the data should be in Structured References aka Tables and not range. 
• Below is a .gif showing the whole process. 

Power_Query

[#7]

Thanks so much for your more detailed instructions. I should be able to do this.