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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Scott Huish

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
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
Aladin Akyurek

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Scott Huish

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
RatExcel: That won't work if the first cell is blank.
 
Upvote 0
Jonmo1

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
J

JayBurn

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

M
  • Question
populate last value in textbox for duplicates items across sheets based on selected combobox
Replies
0
Views
173
Maklil
M
An Quala
  • Question
Delete the entire row if it's column does NOT have specific value
Replies
1
Views
441
An Quala
An Quala
A
  • Question
import data from web like color, name and model
Replies
0
Views
492
alashawi
A
E
  • Question
Simple transaction >> Mail Merge >> Invoice
Replies
1
Views
360
eyz4eva
E
R
  • Question
Need support with calculation logic
Replies
1
Views
192
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,191,516
Messages
5,987,014
Members
440,074
Latest member
Emmanuelian

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
Top