Conditional Formatting with Two Criteria

[ej2rms13]

I am having some issues comparing two sets of two columns. I have two data sets (1 & 2) and they each have two columns (A&B and C&D). I am trying to find the duplicates between the the two data sets and highlight the row. So whatever is in A&B has to match C&D.

Typing everything out in a cell before plugging into conditional formatting, I came up with this: =COUNTIFS(A:A,C:C,B:B,D)>0
That code appeared to work with the rest of the data (controlled test group) by putting True for the duplicates. Plugged the same code into an actual data set and it looked like it only came back true if the values were on the same row (unlike before). Also when plugging the code into conditional formatting on the test data, nothing would highlight.

I am at a bit of a loss, ive been searching different methods for hours and nothing has really done it. Just looking for a little guidance. Thanks

 

[SkipVought]

Hi,

SELECT the DATA in A:B and open the Conditional Formatting Wizard

Enter this formula:
[tt]
=MATCH($A1&$B1,$C:$C&$D:$D,0)>0
[/tt]
Add your FORMAT and OK

SELECT the DATA in C and open the Conditional Formatting Wizard

Enter this formula:
[tt]
=MATCH($C1&$D1,$A:$A&$B:$B,0)>0
[/tt]
Add your FORMAT and OK


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ej2rms13]

Hi Skip,

Looks like it worked pretty well! Thanks for your help! Just as a side question...I built a macro to retrieve this data. Is it possible to find all the duplicates and highlight them through VBA (add on to my macro)? Are there any benefits of doing this through VBA or is it just easier to use conditional formatting?

Regards,

Alex

 

[SkipVought]

“I built a macro to retrieve this data.“

Retrieved from where?

If you have a Table containing data somewhere, why are you getting it via a macro? Why not a query, where you could quite easily SELECT DISTINCT in one SQL statement?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ej2rms13]

I am getting the data from a Reflection application.

Basic operation of macro:

Clear screen > go to specific page > enter assembly part number to retrieve bill of materials > copy part numbers and associated data > paste in excel > continue until end of BOM is reached.

The same goes for the second assembly part number.

The reason I asked about highlighting via macro is because it seems as though conditional formatting is very slow for scrolling. Im guessing that it only does what is shown on the screen and constantly operating as you scroll.

 

[SkipVought]

So you scrape your terminal emulator and put all the data into a tabular format.

Why not just use the remove duplicates feature in Excel once you’ve loaded your data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Just curious what ERP system is your BOM is stored in?

In the past, I’ve extracted multi-level BOMs (parent-child detail) for a number of applications directly from rehosted production tables.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ej2rms13]

For certain applications it is beneficial to see the difference between bill of materials. The two conditions (take A&B for example) give the part numbers their description. The two BOMs being compared should have roughly the same parts/descriptions (not necessarily in the same order) however one BOM might have the most recent part number vs the other BOM. I will not be the only one using this macro so I am trying to make it as user friendly as possible.

 

[SkipVought]

Conditional Formatting will be faster. I’d suggest that each of the BOM tables you produce be a Structured Table, each Table with a name like BOM1 & BOM2.

So let’s say that the Table Headings for the BOM1 Table, columns A & B are PN & Nom. The BOM2 Table (with at least ONE empty column between tables) will have the same Heading Names.

You create your Structured Tables ONE TIME. After that you just replace BOM data with new BOM data.

Your new CF formulae will be...
[tt]
=MATCH(BOM1[PN]&BOM1[Nom],BOM2[PN]&BOM2[Nom],0)>0
=MATCH(BOM2[PN]&BOM2[Nom],BOM1[PN]&BOM1[Nom],0)>0
[/tt]

Did this from my iPad so I haven’t checked my formulae.

You might need to define new range names for the Structured Table notations, in which case the 4 range names and range references would be...
[tt]
rThisPN1: =tBOM1[@PN]
rPN_1: =BOM1[PN]
rThisNom1: =tBOM1[@Nom]
rNom1: =BOM1[Nom]
rThisPN2: =tBOM2[@PN]
PN_2: =BOM2[PN]
rThisNom2: =tBOM2[@Nom]
rNom2: =BOM2[Nom]
[/tt]
...and the CF formulae...
[tt]
=MATCH(rThisPN1&rThisNom1,rPN_2&rnom2,0)>0
=MATCH(rThisPN2&rThisNom2,rPN_1&rNom1,0)>0
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]