Matching data across two sheets to allow conditional formatting of cells

[lojik]

Hi all,

I have tried to search for this but have had no luck, I am ok matching on the same sheet or matching specific cells to cells but my query is a little more complicated and I am unsure how to go about doing this one.

I have two sheets, Headings below

Sheet 1 (Yesterdays Orders)
Headings:

[TABLE="width: 1390"]
<tbody>[TR]
[TD="class: xl65, width: 90"]Order #[/TD]
[TD="class: xl65, width: 200"]Supplier[/TD]
[TD="class: xl65, width: 79"]SAC Received[/TD]
[TD="class: xl65, width: 45"]Mode[/TD]
[TD="class: xl65, width: 50"]Load[/TD]
[TD="class: xl65, width: 50"]Disch[/TD]
[TD="class: xl65, width: 57"]Departure Vessel Cutoff[/TD]
[TD="class: xl65, width: 60"]ETD[/TD]
[TD="class: xl65, width: 60"]ETA[/TD]
[TD="class: xl65, width: 144"]Arrival Vessel[/TD]
[TD="class: xl65, width: 95"]Arrival Voyage[/TD]
[TD="class: xl65, width: 130"]Container[/TD]
[TD="class: xl65, width: 75"]Cont. Mode[/TD]
[TD="class: xl66, width: 60"]Packs[/TD]
[TD="class: xl66, width: 60"]Act. Volume[/TD]
[TD="class: xl65, width: 135"]Shipment Brokerage #[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (Todays Orders)

Order #

Supplier

SAC Received

Mode

Load

Disch

Departure Vessel Cutoff

ETD

ETA

Arrival Vessel

Arrival Voyage

Container

Cont. Mode

Packs

Act. Volume

Shipment Brokerage #

<tbody>
</tbody>

• The headings are identical
• the sheets will have in the region of 1000 lines of data
• The Order # is an identifiable number so will be the only heading that will not have duplication's

What I need to do:

I need Sheet 2 to highlight any cells that are different from Sheet 1 based on the order numbers

E.G
Sheet 1:

[TABLE="width: 1390"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Supplier[/TD]
[TD]SAC Received[/TD]
[TD]Mode[/TD]
[TD]Load[/TD]
[TD]Disch[/TD]
[TD]Departure Vessel Cutoff[/TD]
[TD]ETD[/TD]
[TD]ETA[/TD]
[TD]Arrival Vessel[/TD]
[TD]Arrival Voyage[/TD]
[TD]Container[/TD]
[TD]Cont. Mode[/TD]
[TD]Packs[/TD]
[TD]Act. Volume[/TD]
[TD]Shipment Brokerage #[/TD]
[/TR]
[TR]
[TD]PO-049404[/TD]
[TD]IMP&EXP GROUP CO.LTD[/TD]
[TD]YES[/TD]
[TD]AIR[/TD]
[TD]CNSHA[/TD]
[TD]AUMEL[/TD]
[TD][/TD]
[TD]02-Sep-15[/TD]
[TD]04-Sep-15[/TD]
[TD][/TD]
[TD]BI054[/TD]
[TD][/TD]
[TD]LSE[/TD]
[TD]51[/TD]
[TD]3.36[/TD]
[TD]S00000001[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:

[TABLE="width: 1390"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Supplier[/TD]
[TD]SAC Received[/TD]
[TD]Mode[/TD]
[TD]Load[/TD]
[TD]Disch[/TD]
[TD]Departure Vessel Cutoff[/TD]
[TD]ETD[/TD]
[TD]ETA[/TD]
[TD]Arrival Vessel[/TD]
[TD]Arrival Voyage[/TD]
[TD]Container[/TD]
[TD]Cont. Mode[/TD]
[TD]Packs[/TD]
[TD]Act. Volume[/TD]
[TD]Shipment Brokerage #[/TD]
[/TR]
[TR]
[TD]PO-049404[/TD]
[TD]IMP&EXP GROUP CO.LTD[/TD]
[TD]YES[/TD]
[TD]AIR[/TD]
[TD]CNSHA[/TD]
[TD]AUMEL[/TD]
[TD][/TD]
[TD]02-Sep-15[/TD]
[TD][COLOR=#b22222]05-Sep-15[/COLOR][/TD]
[TD][/TD]
[TD][COLOR=#b22222]BI053[/COLOR][/TD]
[TD][/TD]
[TD]LSE[/TD]
[TD]51[/TD]
[TD]3.36[/TD]
[TD]S00000001[/TD]
[/TR]
</tbody>[/TABLE]

Problems:

Data in sheet 2 will not be in an identical list to Sheet 1, as to say there may be additional or less orders on Sheet 2 meaning the physical order in which it is shown will differ from sheet 1 so a basic =if(and) formula on a column next to each data column will not work, the identifiable number will be the order number so using that as a "lookup" will be required

I am just unsure how to go about this

Thanks for you help

Jack

 

[lojik]

worked it out, using countifs functions to determine if there is a match or not and using that to determine conditional formatting