I have two different Google Sheets that I’m trying to work with:
Registration Data Sheet:
Has columns for First Name, Last Name, Club Name, and weekday columns (Monday, Tuesday, etc.)
Same person can show up in multiple rows if they joined different clubs
Master List Sheet:
Shows each person only once (unique First and Last Name combinations)
Want to fill this with all club activities for each person (like Sarah doing Soccer on Monday, Art on Wednesday, Tennis on Friday)
My Issue:
When I try to match names between these sheets, my formula keeps replacing the previous club info whenever it finds a new match. What I really need is to add each new club to what’s already there, either by putting them all in one cell with commas or spreading them across different cells.
I tried using Filter and INDEX functions but they only give me the most recent match they find. How can I combine all the club entries for each person instead of just getting one result?
actualy ran into this exact problem last month! instead of formulas, try using pivot tables - way easier imo. go to insert > pivot table, add first/last names as rows and club names as values. set it to COUNTA instead of sum and you’ll see all clubs per person. then just copy the results back to your master sheet. works like a charm and dosnt break when you add new data
Had a similar situation when managing sports leagues across multiple venues. The key is using TEXTJOIN combined with IF to concatenate all matching entries. Try this approach: =TEXTJOIN(“, “,TRUE,IF(($A$2:$A$100=A2)*($B$2:$B$100=B2),$C$2:$C$100,””)) where columns A and B are your names and C is club data. You’ll need to enter it as an array formula with Ctrl+Shift+Enter in older Sheets versions. This will grab all club matches for each person and join them with commas in a single cell. Just make sure your range covers all your registration data. The formula checks both first and last names simultaneously, so you won’t miss any combinations or get partial matches.
Query function saved me tons of headaches with this type of data consolidation. What you want is something like =QUERY(RegistrationData!A:D,“select A,B,C where A is not null”,0) to pull all the raw data first, then use another QUERY to group it: =QUERY(your_range,“select A,B,group_concat(C) group by A,B”,0). The group_concat part automatically combines all club names for each person with commas. Just replace A,B,C with your actual column references. This approach handles duplicates better than most other methods and automatically updates when you add new registration data. Been using this method for membership databases for about two years now and it’s rock solid. Make sure your registration sheet doesn’t have any blank rows in the middle though, as that can mess up the query results.