column comparison, trying to identify identify identical columns.

[Jowel]

Hi
test of column comparison.xlsx is an example sheet off what I am trying to do.
I have atm 8 different "markets" that is each represented by a column. In those columns I get different ranges of data and the data in each cell might vary, but it is always presented in alphabetical and numerical order.

I need a way to know if two of the markets have the identical data, in the example 1,3 and 6 have identical data and so does 2 and 4.

It could be done as in this example: =IF(AND(A2=B2;A3=B3;A4=B4;A5=B5;A6=B6;A7=B7;A8=B8;A9=B9;A10=B10;A11=B11;A12=B12);"Yes";"No")
And put into matrix stating which columns matches.

The two questions i have are:

1. Is there any simpler way then doing the if formula to check if the match each other. Reason I want an alternative is that the real document is bigger then the example and it would become a long if formula, and take alot of time doing it.
2. Is there a way to get the result saying identical markets, 1, 3, 6?

Grateful for any help I get.

[benishiryo]

hi Jowel, try this in J3:

Please Login or Register  to view this content.

you would probably need VBA for your 2nd request. try posting in the Excel Programming section if you can't get help here

[oeldere]

With the macro below.

After that a pviot table.

See the attached file.

Please Login or Register  to view this content.

[Jowel]

Thank you so much benishiryo it works really well.