Consolidating Row Entries if a unique identifier (ref #) is on both rows

Cassie_Hall

Hi!

I have a Form in which it allow various request types (Validation, Date Change, Substitution, and Cancellation).

Has anyone created a solution to merge these rows together (possibly into another sheet ) so you can then capture the complete picture of the customer request if change requests were submitted after the initial validation of information.

It currently generates a new row per entry on main worksheet which works for the 1st use case. However, I am trying to find a scalable solution for the 2nd use case in which we need to notify stakeholders a summary of the final deliverable. I was going to use a workflow "message" but this doesn't work with multiple rows.

I look forward to hearing your ideas/solutions!

Note: I already spoke with SS support

Thank you!

Cassie

Genevieve P.

Hi @Cassie_Hall

Do you have a second sheet with all possible Ref Numbers listed? If so, you could use a JOIN(COLLECT cross-sheet formula to gather all matching cell data from the intake sheet into one cell. This means if you had two submissions, both pieces of content would appear in the same place.

See: Formula combinations for cross sheet references

If you don't want to see duplicates if the same data is entered twice, I would suggest writing the formulas in a Multi Select cell, like so:

=JOIN(COLLECT({Column with values to return}, {Ref Number Column}, [Ref Number]@row), CHAR(10))

The CHAR(10) at the end is what separates multi select values. However if you have the same one (e.g. the same status of "In Progress") then it won't repeat that word.

Would this work for you?

Cheers,

Genevieve