Compare 4 columns to find duplicate rows and copy one column data from all duplicate rows

[pacchiee]

ID	First Name	Last Name	State	Reference
1	Mark	Brown	CA	2257849635
2	Shawn	Jack	CA	2245787962
3	Smith	Black	CA	7789654123
4	Mark	Brown	CA	2257849635
5	Smith	Black	CA	7789654123
6	Mark	Brown	CA	2257849635

<tbody>
</tbody>

I have huge data (10,00,000 rows) in the above format. Need to find all the IDs having duplicate content.

For example, the rows 1, 4 and 6 has same data; similarly 3 & 5 are duplicate rows.

Wanted a macro to do the following:
1. Delete all unique rows contents except ID.
2. Find duplicate rows and paste the IDs of duplicate rows next to the Reference column. Like "4,6" for "1".
3. Delete all the contents of duplicate rows except ID.


Finally, the sheet should look like this:

ID	First Name	Last Name	State	Reference
1	Mark	Brown	CA	2257849635	4,6
2
3	Smith	Black	CA	7789654123	5
4
5
6

<tbody>
</tbody>

I tried other manual methods, but due to huge data the file freezes. I know nothing about macros to write one. Please help me with this. Thank you.

 

[BGY23]

Do you need to compare all 4 columns or id the reference unique to the person. If not do you have another column with a unique reference per person?

 

[pacchiee]

The only unique data is ID. Chances are there that all other 4 columns are similar. Hence, we need to compare all 4 columns to find duplicate rows.

 

[BGY23]

I can't write macro's for you, not my thing.

Assmue your data is in A1:E7;

Insert 2 now columns at A
B2 =CONCATENATE(D2,E2,F2,G2,)
A2= =COUNTIF(B$1:B4,B4)

Its importatnt to note that the B1 in A2 is anchored with $. Copy the formula's down and filter anything that isn't a one in column A and clear the contents of cells D:G Good luck

<tbody>
</tbody><colgroup><col></colgroup>

 

[BGY23]

A2 formula should be =countif(B$1:B2,B2)