Highlighting numbers that are the same

Microsoft Office 2007 home and student
May 11, 2011 at 09:01:30
Specs: Windows XP
I have two colunms of numbers in excel, what i wanted to do is highlight the numbers that i the same on the sheet, instead of looking through the sheet, is this possible.

Thanks


See More: Highlighting numbers that are the same

Report •

#1
May 11, 2011 at 10:28:36
You can use Conditional Formatting to get what you want.

If your data is in Column A and Column B:

Conditional Formatting 2007

First Column A:

1) Select your range of cells: A1 thru A14

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(ISERROR(MATCH(A1,$B$1:$B$14,0)),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select your first pretty color, IE Red

9) Click OK

10) Click OK

Now for Column B

1) Select your range of cells: B1 thru B14

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(ISERROR(MATCH(B1,$A$1:$A$14,0)),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select your next pretty color, IE Green

9) Click OK

10) Click OK

The cells in column A that have a match in column B will turn Red
The cells in column B that have a match in column A will turn Green

The above are set for only 14 rows, change to suit your needs.

MIKE

http://www.skeptic.com/


Report •

#2
May 11, 2011 at 10:40:22
Here's a fun way...and you'll learn multiple things all at once.

Read the Excel Help files on Conditional Formatting.

Then, follow this process for Excel 2003. The concept is the same for 2007/2010, but the steps are slightly different.

e.g You want to compare A1:A5 with B1:B5 and highlight any matching values.

Select A1:A5

Choose Format...Conditional Formatting...Formula Is:

=NOT(ISERROR(MATCH(A1,$B$1:$B$5,0)))

Choose a Fill pattern.

Since you selected A1:A5, the Conditional Formatting will be applied to each cell individually. Whenever the formula returns TRUE for a given cell, that cell will be formatted with the format you chose.

How it works:

The MATCH function will try to match e.g. A1 with the values in B1:B5. If it doesn't find it, it will return an error.

The ISERROR function will check to see if the MATCH function returned an error. If the MATCH function finds the value, the ISERROR will return FALSE since it's not an error.

The NOT function will "negate" the FALSE and make it TRUE.

In other words, if MATCH doesn't return an error, then the value must be in the list and the formula will return TRUE. Therefore your cell will be highlighted.

Select B1:B5 and repeat the process, "reversing" the formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 11, 2011 at 10:51:24
Mike,

We've talked about this before. It's that habit thing, remember? ;-)

The use of IF functions in CF usually means more keystrokes. I'm getting old and lazy and only have a limited number of keystrokes left.

Yours:

=IF(ISERROR(MATCH(B1,$A$1:$A$14,0)),TRUE,FALSE)

Mine:

=NOT(ISERROR(MATCH(A1,$B$1:$B$5,0)))

That's 11 keystrokes I can use somewhere else. ;-)

Hey wait..I can save an additional 12 keystrokes if I use this:

=MATCH(A1,$B$1:$B$5,0)>0

That'll also return TRUE if a match is found.

(Just having fun!)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 11, 2011 at 12:46:22
markwebb

Use DerbyDad03's formula, I gave you formulas for finding a Unique number in a list, IE.

If the number is in Column A but not in Column B it will turn Red
If the number is in Column B but not in Column A it will turn Green

Sorry.

MIKE

http://www.skeptic.com/


Report •

Ask Question