INDEX(MATCH or JOIN(COLLECT ?

Whalstead1

Hello,

Thank you to everyone for you help as always. I asked this yesterday, but I think I was asking it the wrong way. Below is a screen show of a sample sheet. This data comes in from a form, so each form gets a new row in the sheet.

What I am looking to do is to combine the rows (values in the cells) if the values in Event and Report Date are the same. I would like to do this into a new sheet.

Below is a screen shot of what I would like the output to be. Basically rows 1 and 2 would be combined because they both are Event 1 (Event Column) and they are both 6/18/2024 (Report Date).

I am not sure if this can be done. I was not sure if INDEX(MATCH should be used or JOIN(COLLECT, etc. Any help is greatly appreciated.

Whalstead1

Thank you both! Adam - that worked perfectly. I was going to populate the second sheet using formulas to grab the unique name. I just need to figure out how to add in the date so they are the same line.

Adam Murphy

You didn't say how you will populate the Event and Report Date fields in the second sheet, so ignoring that for now, you would use join/collect to get the names that have Event 1 and 6/18/2024, such as =JOIN(COLLECT({MainSheet Name}, {MainSheet Event}, Event@row, {MainSheet ReportDate}, [Report Date]@row), ",")

bisaacs

Hey @Whalstead1,

I think you can use JOIN/COLLECT to do this. This will collect and join the names that are next to "Event 1" and have a Report date of 6/1/24:

=JOIN(COLLECT(Name:Name, Event:Event, "Event 1", Date:Date, DATE(2024, 6, 1)), ", ")

This will collect and join the City:

=JOIN(COLLECT(City:City, Event:Event, "Event 1", Date:Date, DATE(2024, 6, 1)), ", ")

Then you can use a similar formula for the State. Since this is being collected on a different sheet, you'll just want to adjust the sheet references.

Hope this helps!

Whalstead1

Thank you both! Adam - that worked perfectly. I was going to populate the second sheet using formulas to grab the unique name. I just need to figure out how to add in the date so they are the same line.