INDEX(MATCH or JOIN(COLLECT ?

Options

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.

Best Answer

  • Whalstead1
    Answer ✓
    Options

    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.

Answers

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭
    Options

    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
    bisaacs ✭✭✭✭✭
    Options

    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!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Whalstead1
    Answer ✓
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!