Conditional Formatting

[ipausa]

Background: I have a worksheet with different unit types. Each unit type has specific dimensions (width(W) x height(H)). Some of the units are entered in as WxH and others HxW. I'm trying to highlight units that have the same square footage but do not have the same dimensions. For example, if if there is a 200'x50' and a 50'x200', they will both have a squarefootage of 10,000. There is no need to highlight this info because I can count them as the same unit type, but I cannot change the sizes to match each other because they are different and I don't want to lose track of that. My problem arises when I have a 100'x100' unit that results in the same, 10,000 sqft. I need to highlight this unit because it is unique and needs to be manually adjusted for further processing. How can I highlight the 100'x100' unit without highlighting the 200'x50' & 50'x200' units?

The best I could figure out is that I probably need to be using the countifs function, but beyond that, I'm clueless. See example attached for additional details. Any help on this would be greatly appreciated. Thanks in advance.

Book2.xlsx

[ipausa]

It's amazing. I've been working on this for hours. I post on here and then immediately after, I have my "ah ha" moment and figure out the solution...

I added some helper cells that take the MIN & MAX value of the dimensions. Thus, if it's a 50x100 and a 100x50, it will put both of those in as 50x100's in the helper cells. Then I used countif>1 for counting the square footages and countif =1 for the concatenates. Then placed a formula that spits out "1" if both of those entry's come back as true and applied condition formatting to that number. See attached for solution.

Book3.xlsx

[ipausa]

One major problem I just discovered in my "solution" is that if I have multiple 50x200's, it will not produce a 1 in H21... and doesn't highlight the issue that needs manual fixing.

[FDibbins]

Hi

I added 2 helpers F and G and used these copied down...
F2=MAX(B2:C2)
G2=MIN(B2:C2)
and then based my CF on this...
=COUNTIFS($F$2:$F$9,F2,$G$2:$G$9,G2)<2

This will highlight the 1's you currently have as unhighlighted - if thats the way you want itm change it from <2 to >1

[Ace_XL]

Do away with the helper columns..Use this in CF rule

=SUMPRODUCT(($A$2:$A$9=$A2)*($B$2:$B$9=$C2)*($C$2:$C$9=$B2))+SUMPRODUCT(($A$2:$A$9=$A2)*($B$2:$B$9=$B2)*($C$2:$C$9=$C2))-(B2=C2)>1

format

[ipausa]

Hi

I added 2 helpers F and G and used these copied down...
F2=MAX(B2:C2)
G2=MIN(B2:C2)
and then based my CF on this...
=COUNTIFS($F$2:$F$9,F2,$G$2:$G$9,G2)<2

This will highlight the 1's you currently have as unhighlighted - if thats the way you want itm change it from <2 to >1

I don't think I'm understanding your solution as I am unable to get it to produce the result I'm trying to achieve. I don't know whether I explained the original issue incorrectly or whether I'm just simply not understanding your solution. When I enter in what you have above, I receive "TRUE"s in row's 6 & 7. I'm trying to seperate row 9 from the rest since it's unique but displaying as not unique due to it sharing the same squarefootage as rows 4 & 5.

[ipausa]

Do away with the helper columns..Use this in CF rule

=SUMPRODUCT(($A$2:$A$9=$A2)*($B$2:$B$9=$C2)*($C$2:$C$9=$B2))+SUMPRODUCT(($A$2:$A$9=$A2)*($B$2:$B$9=$B2)*($C$2:$C$9=$C2))-(B2=C2)>1

format

When using that for the formula in CF, I receive rows 2,3,4,5,8,9 as highlighted which does not produce the result I'm trying to achieve.

[FDibbins]

See the attached...

[ipausa]

See the attached...

This still isn't what I'm trying to achieve. In the same excel sheet you attached, I'm trying to highlight the row with Type 2 50x200 (row 9) because, even though it has the same square footage as Type 2 100x100, it is not the same. I must have explained what I was trying to achieve incorrectly and for that I apologize.

[FDibbins]

I have re-applied my sugvgestion to your file, but have created a 3rd table, below your 2nd table, and apart from the 10000 in D21, all shading is identical. Is d21 the cell you are talking about?

[ipausa]

This works! Thank you guys for the help.