Highlighting and Consolidating Quadruplets

**james_evans** · 06-20-2011, 03:58 PM

Hi All,

I'm working with quite a large data set (>20,000 entries), consisting of two corresponding columns. The first column contains a number/letter ID which in some cases is duplicated up to six times. I would like to highlight all rows for which the ID is repeated four or more times, delete all other rows, and create a new column consisting of only one instance of each ID which in the original column was duplicated four or more times. Is there any way to accomplish this without painstakingly filtering and identifying these quadruplets manually?

I'm quite a beginner with Excel, so any and all suggestions would be greatly appreciated. I'm currently working with Excel 2008 for Mac -- however, if necessary, I could import the data into Excel 2007 for Windows.

Cheers,

James

**daddylonglegs** · 06-20-2011, 04:06 PM

Hello James,

This formula will identify those entries in A2:A25000 that repeat 4 or more times

=IF(COUNTIF(A$2:A$25000,A2)>=4,"X","")

but you might want to just highlight the first row of the +4 repeats. This formula will do that

=IF(AND(COUNTIF(A$2:A$25000,A2)>=4,COUNTIF(A$2:A2,A2)=1),"X","")

copy down column then filter by the new column

Note that the second COUNTIF changes as you copy down, that's deliberate

**daddylonglegs** · 06-20-2011, 04:13 PM

....or I suppose, if it doesn't matter which row of the 4-6 you highlight you could just identify the 4th repeat, i.e.

=IF(COUNTIF(A$2:A2,A2)=4,"X","")

**CTRLZ** · 06-20-2011, 04:22 PM

To get all the 4x names into one column:

Assuming all your data starts in A2, put:
in cell b2: =IF(COUNTIF(A$1:A1,"="&A2)>0,"",IF(COUNTIF(A:A,"="&A2)>=4,COUNT(B$1:B1)+1,""))
in cell c2: =IF(CELL("row",B2)>COUNT(B:B)+1,"",INDEX(A:A,MATCH(CELL("row",B2)-1,B:B)))

Autofill those the length of the data and column C will fill with the ones that match four times. Change the columns and rows of the formulas to match your data or just make a new sheet with the names starting in a2. You'll have to start in row 2 or later.

**james_evans** · 06-21-2011, 12:26 AM

Thank you very much for the suggestions -- I really appreciate such prompt responses!

I ended up using your second solution, daddylonglegs (i.e. using the specified function to mark every fourth duplicate), and it worked like a charm.

I'll definitely be returning to these forums in the future with any excel-related questions (and I'm sure there will be many!).

Cheers,

James