I want to write a formula that detects duplicate names.

David Noël

 If there is a duplicate in the Name column, I want it to merge the 'Event' values into the 'Event History' row.

sharkasits

If you want all lines to have the complete list....

=JOIN(Collect([Event]:[Event], [Name]:[Name], [Name]@row),";")

If you want the new line to have everything prior to it + current event...

You will need an Auto Number column (e.g., RowID) and use.

=IF(Count(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row), [RowID]:[RowID], <= [RowID]@row)>1 , Index(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row, [RowID]:[RowID], <= [RowID]@row), Count(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row, [RowID]:[RowID], <= [RowID]@row)-1) + ";" + [Event]@row , [Event]@row)

Hope that heps!

Ibrahim Khaleel

Dear David,

Please Add new column as a "Duplicate Check" and enter below formula,

=IF(COUNTIFS([Name]:[Name], [Name]@row, [Name]:[Name], NOT(ISBLANK(@cell))) > 1, 1)

Apply conditional formatting on Name column, check if Duplicate Check column = 1 then change the color to get notice.

Check below snapshot

Ibrahim Khaleel

Ibrahim Khaleel

I hope this will help you to resolve duplicate check.

David Noël

Thank you Ibrahim but this does not merge the data.

If the name column has any matches, I want the Event History column to display, in the screenshot example, all event data form Jim John so in the Event History column, it would show GDC 2023 (from line 1) and GDC 2024 (from line 3). So, merge data from the Event column into Event History if the Name column has duplicates.

sharkasits

If you want all lines to have the complete list....

=JOIN(Collect([Event]:[Event], [Name]:[Name], [Name]@row),";")

If you want the new line to have everything prior to it + current event...

You will need an Auto Number column (e.g., RowID) and use.

=IF(Count(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row), [RowID]:[RowID], <= [RowID]@row)>1 , Index(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row, [RowID]:[RowID], <= [RowID]@row), Count(Collect([Event History]:[Event History], [Name]:[Name], [Name]@row, [RowID]:[RowID], <= [RowID]@row)-1) + ";" + [Event]@row , [Event]@row)

Hope that heps!