Conditional formatting - maybe?

bds

Board Regular
Joined
Jul 7, 2007
Messages
84
I have a worksheet in Excel 2002. That worksheet has many rows of data. In column A of that data are 9 digit numbers. Often, there are two or three rows with the same 9 digit number. I have sorted the data from lowest to highest, so when the numbers are the same, they are one after the other. However, the report is hard to read. I'd like to somehow make is so there is a distinction between rows where the numbers differ. So, if the first 2 numbers match each other, then those rows could be white - and then the next 3 match each other and those 3 would be gray - and then the next two match either other and they would be white, etc. Alternatively, I'd be happy to insert a blank row whenever a new number shows up in sequence. Is there a way to do this?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Well, I don't know about alternating the color, but you can definately highlight any that are dups with conditional formatting.

formula is

=COUNTIF(A:A,A1)>1
 
Upvote 0
Assuming you've already sorted your data based on this 9 digit number, you could identify duplicates in conditional formating.

For example, assume your 9 digit numbers are in Col A, starting on row 2.

Set your default format for the entire range, for example black text on a white background.
Then, in conditional formating, set your first condition for cell A2 as
Formula is,
Code:
=or(a2=a1,a2=a3)
and format as required, eg red text on a yellow background.
Note this will only identify duplicates - it will treat a pair of duplicates the same as three or more duplicates - if you want to treat pairs differently from triplets, you'll need something else.
 
Upvote 0
Note that although both of these will work, jonmo1's solution is better, because you don't need to pre-sort your data - it will identify duplicates wherever they are in the column.
 
Upvote 0
I don't think I've explained myself clearly - or I don't understand the responses correctly. I know there are duplicated. I want to color the first set of duplicated one color (say white background with black text) and the 2nd set of duplicated another color (say gray background with black text) and then the 3rd set of duplicates would be white background with black text and the 4th set would be gray backround with black text and so forth. That way, when I read the info on the sheet, I know to compare the first 2 lines, then compare the next 3 lines, etc.
 
Upvote 0
You could do this with a helper column.

For example, still assuming your 9 digit number is in Col A, starting on row 2, and that you have already sorted your data.

In cell B2, insert a 1.

Put this formula in Col B starting on row 3-
Code:
=if(a3=a2,+b2,+b2*-1)
and copy down as required.
This will put either a 1 or a -1 against every entry. Every time the 9 digit number changes, the number in Col B will flip between 1 and -1, and all entries with the same 9 digit code will have the same entry in Col B.
Then, use Col B as the basis for your conditional formating - for example
Code:
=b2=1
and format as required
 
Upvote 0
clever idea. Thank you. I am constantly amazed at how many talented people are willing to help folks with this program. I appreciate it. [/code]
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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