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