admin管理员组文章数量:1400776
I'm working with two Google Sheets:
Club Registers Sheet:
Contains columns for First Name, Last Name, Club Name, and day identifiers (e.g., Monday, Tuesday, etc.).
A person's name can appear multiple times if they are registered for multiple clubs.
Summary Sheet:
Contains a unique list of all people (by First and Last Name).
I need to populate this sheet with all the clubs that each person attends (e.g., Alex has Football on Monday, Baseball on Tuesday, Colouring on Thursday).
Problem: When trying to match the names between the two sheets, my current approach overwrites the cell's previous data when a new match is found. Instead, I want to append each new club entry to the existing data (either by placing them in the same cell with a separator or in separate cells).
I attempted using functions like Filter or INDEX, but they always return the last result they find.
I'm working with two Google Sheets:
Club Registers Sheet:
Contains columns for First Name, Last Name, Club Name, and day identifiers (e.g., Monday, Tuesday, etc.).
A person's name can appear multiple times if they are registered for multiple clubs.
Summary Sheet:
Contains a unique list of all people (by First and Last Name).
I need to populate this sheet with all the clubs that each person attends (e.g., Alex has Football on Monday, Baseball on Tuesday, Colouring on Thursday).
Problem: When trying to match the names between the two sheets, my current approach overwrites the cell's previous data when a new match is found. Instead, I want to append each new club entry to the existing data (either by placing them in the same cell with a separator or in separate cells).
I attempted using functions like Filter or INDEX, but they always return the last result they find.
Share Improve this question edited Mar 25 at 13:37 Mayukh Bhattacharya 27.8k9 gold badges29 silver badges42 bronze badges asked Mar 25 at 9:30 Sikai ZhangSikai Zhang 11 bronze badge 1- I see that you are a fairly new user here at StackOverflow. Please do read how to ask first before asking another question as your post might be closed if it doesn't meet the criteria of it being a good question. Enjoy your stay at StackOverflow. Also, please provide a minimal reproducible example like markdown tables containing your sample input data. – Babanana Commented Mar 25 at 10:14
1 Answer
Reset to default 1Matching Unique Names to a Dataset
I suggest using filter function this way the data provided will be everything that matches the names.
Sample Formula
=LET(names, BYROW('Club Registers Sheet:'!A2:B, LAMBDA(r, JOIN(" ", r))), dataset, HSTACK(names,'Club Registers Sheet:'!C2:D), unqnames, UNIQUE(names), HSTACK(unqnames,BYROW(unqnames, LAMBDA(z, TOROW(FILTER(CHOOSECOLS(dataset,2,3), CHOOSECOLS(dataset,1) = z))))))
The created sample formula is based on the markdown table I created to test it please adjust it accordingly to your need.
Sample Output and Data
It is not clear what your expected output's format is so I opted in on one of your acceptable formats which is divided to each sell or separated by cells.
Club Registers Sheet
| First Name | Last Name | Club Name | Day |
|---|---|---|---|
| Rick | Grimes | Soccer | Monday |
| Sasha | Tyresse | Archery | Monday |
| Maggie | Ri | Debate Team | Monday |
| Rick | Grimes | Debate Team | Tuesday |
| Sasha | Tyresse | Archery | Tuesday |
| Maggie | Ri | Soccer | Tuesday |
| Maggie | Ri | Table Tennis | Tuesday |
Summary Sheet (Formula Result)
| Rick Grimes | Soccer | Monday | Debate Team | Tuesday | ||
| Sasha Tyresse | Archery | Monday | Archery | Tuesday | ||
| Maggie Ri | Debate Team | Monday | Soccer | Tuesday | Table Tennis | Tuesday |
References:
Filter Function
本文标签：
版权声明：本文标题：How can I consolidate multiple club entries for each person in Google Sheets without overwriting data? - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1744206685a2595222.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论