Ignore Blanks in Cell Comparison

J

JayBurn

New Member
Joined
Jun 13, 2013
Messages
44
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...

DescriptionBrand 1Brand 2Brand 3Brand 4Brand 5Same?
Product ACode 1Code 1Code 1Code 1Code 1Equal
Product BCode 2Code 2Code 2Code 2Equal
Product CCode 3Code 3Code 3Code 4Code 3Different
Product DCode 4Code 5Code 5Different

<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?

Thanks in advance!

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.
Sort by date Sort by votes
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>
 
Upvote 0
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"
 
Upvote 0
Non Array entered, assuming the Codes are TEXT values.

=IF(COUNTIF(B2:F2,LOOKUP(REPT("z",255),B2:F2))=COUNTA(B2:F2),"Equal","Different")
 
Upvote 0
Jonmo1 said:
Non Array entered, assuming the Codes are TEXT values.

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


Absolutely perfect! Thanks Jonmo, works a treat. Thanks everyone for the good suggestions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
How to compare 2 columns in one table with 2 columns in another table?
Replies
5
Views
332
JenniferMurphy
JenniferMurphy
S
  • Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
235
STARL0RD
S
M
  • Question
Check if cell is blank with additional criteria
Replies
3
Views
220
Cubist
Cubist
I
  • Question
Divide cell value so weekly stock is balance between warehouses
Replies
1
Views
119
Kaper
Kaper
A
  • Solved
rearranging data based on headers and get rid of merged cells
2
Replies
15
Views
472
kvsrinivasamurthy
K

Forum statistics

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

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top