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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
192
JenniferMurphy
JenniferMurphy
B
  • Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
455
Jon Peltier
J
B
  • Solved
Counting as one when one unique customer purchase item with multiple time in a month
Replies
1
Views
146
Eric W
Eric W
A
  • Solved
merge QTY on textbox based on selected combobox on userform if the adjacent cell doesn't equal zero
Replies
2
Views
138
abdo meghari
A
R
  • Solved
Result from multiple criteria and range between two values
Replies
3
Views
357
rythmic_flow
R

Forum statistics

Threads
1,214,827
Messages
6,121,818
Members
449,049
Latest member
cybersurfer5000

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