Ignore Blanks in Cell Comparison

JayBurn

New Member
Afternoon Gurus.

I'm working on a large document listing prices against product codes, with a view to compare prices across different customer brands. However, i know some of the products don't match up. The products go across five brands, and not all five brands take the product, so there are some blanks...

 Description Brand 1 Brand 2 Brand 3 Brand 4 Brand 5 Same? Product A Code 1 Code 1 Code 1 Code 1 Code 1 Equal Product B Code 2 Code 2 Code 2 Code 2 Equal Product C Code 3 Code 3 Code 3 Code 4 Code 3 Different Product D Code 4 Code 5 Code 5 Different

<TBODY>
</TBODY>

They are standardised text codes, and while I know it's possible to do with a long old nested formula with lots of IF, AND, OR etcs. However, Excel cries about the number of nested levels.

So, is there an easier way to do this other than vast nesting like a flock of house martins?

Jay

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try this:

=IF(MOD(SUM(COUNTIF(B2:F2,B2:F2))/COUNTA(B2:F2),1),"DIFFERENT","EQUAL")

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy down.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:108px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:93px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Description</td><td >Brand 1</td><td >Brand 2</td><td >Brand 3</td><td >Brand 4</td><td >Brand 5</td><td >Same?</td></tr><tr style="height:36px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Product A</td><td >Code 1</td><td >Code 1</td><td >Code 1</td><td >Code 1</td><td >Code 1</td><td >EQUAL</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Product B</td><td >Code 2</td><td >Code 2</td><td > </td><td >Code 2</td><td > </td><td >EQUAL</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Product C</td><td >Code 3</td><td >Code 3</td><td >Code 3</td><td >Code 4</td><td >Code 3</td><td >DIFFERENT</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Product D</td><td >Code 4</td><td > </td><td >Code 5</td><td >Code 5</td><td > </td><td >DIFFERENT</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >{=IF(MOD<span style=' color:008000; '>(SUM<span style=' color:#0000ff; '>(COUNTIF<span style=' color:#ff0000; '>(B2:F2,B2:F2)</span>)</span>/COUNTA<span style=' color:#0000ff; '>(B2:F2)</span>,1)</span>,"DIFFERENT","EQUAL")}</td></tr><tr><td >G3</td><td >{=IF(MOD<span style=' color:008000; '>(SUM<span style=' color:#0000ff; '>(COUNTIF<span style=' color:#ff0000; '>(B3:F3,B3:F3)</span>)</span>/COUNTA<span style=' color:#0000ff; '>(B3:F3)</span>,1)</span>,"DIFFERENT","EQUAL")}</td></tr><tr><td >G4</td><td >{=IF(MOD<span style=' color:008000; '>(SUM<span style=' color:#0000ff; '>(COUNTIF<span style=' color:#ff0000; '>(B4:F4,B4:F4)</span>)</span>/COUNTA<span style=' color:#0000ff; '>(B4:F4)</span>,1)</span>,"DIFFERENT","EQUAL")}</td></tr><tr><td >G5</td><td >{=IF(MOD<span style=' color:008000; '>(SUM<span style=' color:#0000ff; '>(COUNTIF<span style=' color:#ff0000; '>(B5:F5,B5:F5)</span>)</span>/COUNTA<span style=' color:#0000ff; '>(B5:F5)</span>,1)</span>,"DIFFERENT","EQUAL")}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

Also:

G2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(B2:F2<>"",MATCH(B2:F2,B2:F2,0)),COLUMN(B2:F2)-COLUMN(B2)+1),1))

Custom format the formula cells as:

[=1]"Same";"Different"

RatExcel: That won't work if the first cell is blank.

Non Array entered, assuming the Codes are TEXT values.

=IF(COUNTIF(B2:F2,LOOKUP(REPT("z",255),B2:F2))=COUNTA(B2:F2),"Equal","Different")

Non Array entered, assuming the Codes are TEXT values.

=IF(COUNTIF(B2:F2,LOOKUP(REPT("z",255),B2:F2))=COUNTA(B2:F2),"Equal","Different")

Absolutely perfect! Thanks Jonmo, works a treat. Thanks everyone for the good suggestions.

Absolutely perfect! Thanks Jonmo, works a treat. Thanks everyone for the good suggestions.

I think my suggestion is the most robust. If the last text value is a formula blank, LOOKUP will fail.

RatExcel: That won't work if the first cell is blank.

Yeah, you're right. What's important that problem's solved. I would stick to Aladin's solution

Replies
5
Views
332
Replies
4
Views
235
Replies
3
Views
220
Replies
1
Views
119
Replies
15
Views
472

1,221,199
Messages
6,158,488
Members
451,496
Latest member
pledge8236

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back