Forum Discussion
Looking for automated way to build an event attendance list
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 |
6 Replies
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.
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!
- 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.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
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, ", ")) )
