Highlight the cells that match??

**Ltat42a** · 08-26-2011, 08:38 AM

Hello All,
I have a lil project in Excel and having trouble figuring how to make this work. I have a sheet with 16 rows and 32 columns. On each of the rows, I enter a zero or a 1 in each of the 32 columns. What I'm trying to figure out, how can I highlight the columns that have a matching number in each of the columns on each row?? For the columns that have mismatched numbers, leave the cells as is.

See the example...

Thanx...LT

**Domski** · 08-26-2011, 08:47 AM

Hi,

See attached using conditional formatting. I selected C3:AH18 and then in conditional formatting used the formula: =COUNTIF(C$3:C$18,MODE(C$3:C$18))=16 and then set the format.

Dom

**martindwilson** · 08-26-2011, 08:55 AM

weird way of looking at subnets
but

select c3:ah18
apply conditional format of
=OR(SUM(C$3:C$18)=16,SUM(C$3:C$18)=0)

**Ltat42a** · 08-26-2011, 10:15 AM

Thank you guyz.....both versions work pretty good. Now....can we take 1 step further?
On the Subnet Mask row...for each column that has a match, we insert a "1", for each column that DOES NOT match, we insert a "0". Is that possible?? I was trying to come up with a math formula...kinda stumped.

This is more for route summarization than for subnetting.

Thanx again....LT

**Domski** · 08-26-2011, 10:23 AM

Expanding Martin's formula a little should do it: =OR(SUM(C$3:C$18)=16,SUM(C$3:C$18)=0)*1

Dom

**Ltat42a** · 08-26-2011, 10:39 AM

Originally Posted by Domski

Expanding Martin's formula a little should do it: =OR(SUM(C$3:C$18)=16,SUM(C$3:C$18)=0)*1

Dom

That didn't work very well, for the columns that did not match all the numbers, it put in a "1", and it needs to be a zero.
Columns with a match = "1"
Columns with a mismatch = "0"

I was trying an IF statement. Being that the only numbers used in a calculation is either a 1 or a zero. If I multiply each number by the next one, I'll come up with a 1 or a zero.
Then using an IF statement, if the result is a "1", insert a "1", if the result is a zero, insert a "1". This is only for the subnet mask row.

Thanx...LT

**Domski** · 08-26-2011, 10:43 AM

Works okay for me.

Dom

**Ltat42a** · 08-26-2011, 10:44 AM

Was just thinking about my IF statement, don't think that will work right, for the columns that do not match, the end result must = 0.

**Ltat42a** · 08-26-2011, 12:36 PM

Found a malfunction(s). Your example works pretty good, Thank You. If I use 16 network statements, it works good, if I use less than 16 networks, it doesn't work at all. They'll be cases where I may need to summarize only 4 networks, or 6, or 8, etc..