How to compare an entire column to multiple columns in Excel

[paveernaa]

I have an excel sheet with about 200 columns and about 3000 rows of
data. Some of the columns are repeated with same values. I need to
find out the duplicate

Could anyone of you can help me finding a formula that compares each
columns and identify the equal columns:

For example from the dataset given below, column A = Column C

as bc as bc
a1 e2 a1 e8
c8 c8 c8 c8
r8 t9 r8 r6

Thank you.

Regards,
PS
-

 

[Peo Sjoblom]

=SUMPRODUCT(--(C5:C16=D516))=ROWS(C516)

will return TRUE if each cell is equal to the adjacent cell


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[paveernaa]

Thanks Peo. This solution works for Adjacent columns.

My issue is that the column A could match with multiple Columns; ie:
columns B, E,R,T,Z,AA, AZ...etc.

Same testing need to be verified against each of the columns from start
to the end, so that ultimately, all duplicate columns can be removed
from the spread sheet.

Thank you
Regards,
PS

 

[Peo Sjoblom]

You can just change it a bit but if you want to test values against A you
can use this method

http://www.cpearson.com/excel/duplicat.htm

to tag duplicates


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com