How to Compare 2 or more columns

[hiranuk]

Hi

I am trying to compare 2 columns for duplicates. The thing is the duplicates may not appear in the same fields for eg if there is the word "hello" in cell A1 there may not be a "hello" in B1, it may be in B24 for eg.

I want to output the duplicates in the C Column. Im not sure about how to do this.

Any help appreciated.

Thanks

[jason.b75]

Something like

Please Login or Register  to view this content.

[hiranuk]

Thanks. There will be 1000 entries in column A and the same in column B, so do i need to change the formula to say $1000 ?

[hiranuk]

Hi I have attached the workbook. You can see that A4 is the same as B9 but the formula has not outputted this in the C Column. Thanks

[KOKOSEK]

Please Login or Register  to view this content.

not tested so intensively.

[jason.b75]

Thanks. There will be 1000 entries in column A and the same in column B, so do i need to change the formula to say $1000 ?

That is correct, if you will never add any more rows to it then use the exact number. Your example has 205 rows, so if you are likely to add more to it occasionaly, then maybe set the formula to 250, just don't go too high. The formula takes a lot of processing effort to calculate, so setting 1000 when you only need 100 in overloading it unnecessarily.

As for no results in column C, try filling down.