Merge Data from Multiple Rows Based on Email Address in Master Sheet

System

This discussion was created from comments split from: Merging rows in a master sheet with unique identifier.

Tony Oxa

Hello,

I have kind of the same situation. I have the data in the master sheet and need to merge the info in one row based of the email address. Lets say that email address appears three times in three different rows, i need to merge the info in some of the columns like serial #'s into one column.

jmyzk_cloudsmart_jp

Hi @Tony Oxa

Suppose I have a Master Sheet like the image below.

https://app.smartsheet.com/b/publish?EQBCT=3641e56ded324cc18c17ef3ad95bd0f9

First, I would dynamically populate unique emails to the Merged Master View sheet using the INDEX(DISTINCT()) formula.

[Email] =IFERROR(INDEX(DISTINCT({Raw Master Sheet : Email}), [#]@row), "")

Then, using the email, I can collect and join the data from the master sheet, Serial #, for example.

[Serial #'s Combined] =JOIN(COLLECT({Raw Master Sheet : Serial #}, {Raw Master Sheet : Email}, Email@row), ", ")
[First Submission] =MIN(COLLECT({Raw Master Sheet : Date Submitted}, {Raw Master Sheet : Email}, Email@row))
[Last Submission] =MAX(COLLECT({Raw Master Sheet : Date Submitted}, {Raw Master Sheet : Email}, Email@row))

The last two formulas use the MIN and MAX functions to get the First and Last submission.

https://app.smartsheet.com/b/publish?EQBCT=994bd82ba5604463bf6889419a8237cf