Duplication in rows condional formatting

**MAXIM** · 06-10-2010, 05:36 PM

Hi Forum,

Looked though previous posts, but cannot solve my problem.

I have spreadsheet that i need to highlight duplicate rows with CF ideally. All Cells need to match across all columns(A-AB) to qualify as duplicate.For me its a made tricky cuz the sheet has 31000 rows.

I have completed unique filter and saved result but need to show duplicates. The main aim is to keep the sheet in same order to present back to client .

Any suggestions would be a great help.

Thanks in advance

**daddylonglegs** · 06-10-2010, 06:18 PM

Try using a helper column.....

Put this formula in AD2 and copy down all your data

=A2&"-"&B2&"-"&C2&"-"&D2&"-"&E2&"-"&F2&"-"&G2&"-"&H2&"-"&I2&"-"&J2&"-"&K2&"-"&L2&"-"&M2&"-"&N2&"-"&O2&"-"&P2&"-"&Q2&"-"&R2&"-"&S2&"-"&T2&"-"&U2&"-"&V2&"-"&W2&"-"&X2&"-"&Y2&"-"&Z2&"-"&AA2&"-"&AB2

Then you can select the whole range of data, e.g. A2:AB31000 and apply this formula in conditional formatting

=COUNTIF($AD:$AD,$AD2)>1

You can hide the helper column......

**MAXIM** · 06-11-2010, 10:14 AM

Thanks, DLL

Almost there... the results show a few errors but fundamentally ok .The duplications are running very slow , so much so I cannot scroll down the sheet. To paste an example to show the issue is proving difficult , and I cannot use the sheet because of this.

**mstwntd** · 06-11-2010, 11:53 AM

You only keep the formual on the top cell. You copy and paste the values to eliminate the large formula. This will speed up the spreadsheet once you are done. To restate: Follow daddylonglegs advice but when you are done, copy the range and paste values except for the top cell with the formula. You can use this one to repaste as needed in the future. Then the Countif formual should work without being too slow.