Duplicate Lines

  • Thread starter Thread starter universal
  • Start date Start date
U

universal

Hello,

I have a workbook that has 30 columns, and roughly a thousand rows.

Each Row should be unique and is comprised of these 30 different
terms.

I am trying to think of a way to add a 31st column to the end of the
data which checks its respective row against all other rows to check
that none are duplicated and will return FALSE if any match.

Any ideas?


Many Thanks
Ed
 
The following array formula will coompare A3:AD3 to A1:AD1 and A2:AD2
and will return 1 or 2 if row 3 matches row 1 or 2. If row 3 is unique,
then the formula will return #N/A. Copy the formula down through row
1000; any rows where the formula returns a number instead of #N/A is a
duplicate row. For reasons that are not clear to me, this formula will
not work in row 2, so you will have to manually compare the second row
to the first.

=MATCH(A3&B3&C3&D3&E3&F3&G3&H3&I3&J3&K3&L3&M3&N3&O3&P3&Q3&R3&S3&T3&U3&V3&W3&X3&Y3&Z3&AA3&AB3&AC3&AD3,A$1:A2&B$1:B2&C$1:C2&D$1:D2&E$1:E2&F$1:F2&G$1:G2&H$1:H2&I$1:I2&J$1:J2&K$1:K2&L$1:L2&M$1:M2&N$1:N2&O$1:O2&P$1:P2&Q$1:Q2&R$1:R2&S$1:S2&T$1:T2&U$1:U2&V$1:V2&W$1:W2&X$1:X2&Y$1:Y2&Z$1:Z2&AA$1:AA2&AB$1:AB2&AC$1:AC2&AD$1:AD2,0)

This must be array entered (Ctrl-Shift-Enter)

If you were to write a VBA array function called ConcatCols() that would
concatenate by rows all the columns in each row, then this formula could
be simplified to

=MATCH(ConcatCols(A3:AD3),ConcatCols(A$1:AD2),0)

Jerry
 
Back
Top