Compare two columns varying in number of rows on different sheets

[JDS_916]

Hello to all,

I have two sheets, namely 'Company 1' and 'Company 2'. Each sheet has a column (F), namely 'ship number'. Company 1 has 12,500 rows and Company 2 has 350 rows. I would like to check if any of the ship numbers in company 2 are in company 1. If a ship number exists in Company 1, then I would like to highlight that row in Company 1 or least the column/cell, but the row would be really nice. I would then like to filter the results somehow. Many thanks.

Regards

JDS_916

[SamT]

I am out the door in just a minute. I'm sure that someone will be along shortly, but if you want to try for yourself (this is NOT code, just an idea spark for you.)


Dim X As Range
For each Cel in Company 2
Set X = Company 1. Find ( Cel )
If not X is nothing then X.entireRow .Interior.Colorindex = 7
Next

[p45cal]

You can do this with conditional formatting, using a formula, of the ilk:
=ISNUMBER(MATCH($F3,Sheet2!$F:$F,0))
applied to either entire rows, or just several columns (being the extent of the data), then you can filter on colour (depending on which version of Excel you have).

It would be easier for me to show this on a sample file provided by you to save me (a) having to make up a sample file and (b) to save me guessing wrongly anyrthing about your file/sheets/data. Anonymise the data first if it's sensitive data.

[JDS_916]

Hi p45cal,

Thanks very much for responding. I've uploaded the file. As I've tried to explain, if any of the numbers in column F on Company 2 sheet match any of those in column F on Company 1 sheet highlight the number or row on Company 1 sheet.

Regards,

JDS_916

[p45cal]

In the attached, there is conditional formatting applied to A2:K12993. Formula:
=ISNUMBER(MATCH(CHAR(160) & $F2,'Company 2'!$F:$F,0))
It is autofiltered by colour using column F.

This was additionally complicated by the values in Company 2 column F all being prefixed by a non-breaking space (ascii code 160); for this to work properly they must ALL be prefixed by this character. Alternatively, you could remove this character from the cells and obtain a simpler and more reliable conditional format formula.

[JDS_916]

Works like a treat p45cal. Thanks a lot for your time and expertise.

Regards,

JDS_916