Is there a way to merge multiple activity records per individual in Google Sheets without losing information?

Other Questions Google Sheets
1

I’ve got two spreadsheets I’m dealing with in Google Sheets:

  1. Activity Log:
  • Has columns for Name (First and Last), Activity Type, and Days (like Mon, Tues, etc.)
  • People can show up more than once if they do different activities
  1. Overview Sheet:
  • Just a list of everyone’s names (no repeats)

I’m trying to fill in the Overview Sheet with all the stuff each person does. For example, if Tom does art on Mondays and sports on Fridays, I want that all together.

The problem is, when I try to match names between the sheets, it keeps replacing the old info instead of adding to it. I want to either put all the activities in one cell (maybe with commas between them) or in different cells next to the name.

I’ve played around with some functions, but they only give me the last thing they find. Any ideas on how to make this work? I’m kinda stuck!

2

I’ve encountered a similar challenge in my work with activity tracking. One effective solution is to use a combination of ARRAYFORMULA and TEXTJOIN functions. Here’s how you can approach it:

In your Overview Sheet, use this formula:

=ARRAYFORMULA(TEXTJOIN(“, “, TRUE, IF(ActivityLog!A:A=A2, ActivityLog!B:B&” (”&ActivityLog!C:C&“)”, “”)))

Place this in the cell next to each name. It will search the Activity Log for matching names, then combine all activities and days for that person into a single cell, separated by commas.

This method preserves all information and presents it in a concise format. You can adjust the delimiter and formatting within the TEXTJOIN function as needed. Remember to replace ‘ActivityLog’ with your actual sheet name if it’s different.

3

I’ve dealt with this exact issue in my volunteer management system. What worked for me was using a combination of FILTER and CONCATENATE functions.

Here’s the formula I used:

=CONCATENATE(FILTER(ActivityLog!B:B&" (“&ActivityLog!C:C&”)", ActivityLog!A:A=A2), ", ")

This grabs all activities and days for each person, combining them into one cell. It’s a bit tricky to set up at first, but it’s really powerful once you get it working.

One tip: make sure your data is clean before applying this. Any inconsistencies in name spelling can mess up the results. Also, you might want to wrap it in an IFERROR function to handle cases where there’s no match.

Hope this helps! Let me know if you need any clarification on setting it up.

4

hey, i had a similar problem before. u could try using QUERY function. something like:

=QUERY(ActivityLog!A:C, “SELECT B, C WHERE A = '” & A2 & “'”)

this should grab all activities n days for each person. might need to tweak it a bit, but it shoud work. good luck!