How to visually separate rows with same data?

Hello,
I work on very long spreadsheets with many rows and columns, that are sorted by data in column A. Many of the rows have the same data in Column A, so I end up with a 2000 row file, but there are only 500 or so unique items that I work on.

Please help me find a way that I can visually separate rows, so I can quickly see when one set of data ends and another one starts. It can be done by:
- shading every other new string of rows, so I have shaded, non-shaded, shaded, non-shaded... rows
- placing borders between new strings of data, so every time there is a new number in Column A, system automatically inserts a border.

For example:
In Column A (Item SKU number) I will have:
11021
11021
11021
12023
12023
14045
14045
14045
14045
14045
25010
26900
26900
... etc.

My eyes will forever be thankful.

Hello
Am I right in saying you want to highlight the first 11021,12023,14045 etc.? If so you can use a conditional format formula as follows:

=COUNTIF(A1:$A$1,A1)=1

If your example data above begins in A1. Select range and apply formula.

Hope this helps.

Not quite.

- If shading is the only option, I would like to back and forth: shade rows of first group of same SKUs, then not shade the next group of rows, then shade and not shade, back and forth.

- I would however prefer to be able to separate them by a border at the bottom of the last row for each group, because I use cell color fill in other areas of the spreadsheet, so shading the rows will overwrite the other cell color fill.

What makes it crappy to work with now is that I have as many as 50+ columns and I have to scroll to the first one to know when one group of SKUs ends and next one starts. With this I would see each batch separated from the next, regardless of where I was.

Attached is example of the file. As you see SKU numbers in column A are in groups. I separated a few of them with bottom border, just to illustrate what I think would be a perfect solution to my needs.

Problem is that manually it is time consuming to insert these borders when one group of SKUs ends and another one starts, so I hope there is some automated solution to this dilemma.

Screen Shot 2012-04-03 at 12.33.21 PM.jpg

Dear DBY,
Sorry for late reply. Had a baby born and my schedule changed unexpectedly.
There is no reason to not separate he values in my example. I simply missed them when manually adding lines.

Your example is EXACTLY what I was after. However I don't understand how to put in the border in conditional formatting. I enter the formula as you showed, however I don't have an option to add such a line break. I use Mac's version of excel if that makes any difference.

DBY... I got it!! Man... I didn't realize there is a border option in cond. formatting.

THANK YOU!! This place is incredible!

Example_Reply (2).xlsDBY,
I unmarked it as solved as there is one issue with the solution. I hope you can advise a fix:
This works when I have the sheet sorted by SKU number in ascending order. However when I want to work on the sheet and sort it by column C - SKU's Sell Through Rate (which is identical for all the same SKUs) for example, it looses the divisions and no longer same SKUs are separated properly.
Example attached.

Try this,
- Sort the column by Small to big
- In column B2 put the formulla, =if(A2=A1,1,0) (supposing ur data starts from A1 column)
- not do a filter on all the 1s, these are the duplicate values.

Amit.

Hi
Hope the family and new baby are doing well. I'm a little confused, because when I sort you example file by Value 1 in column C, either ascending or descending, the SKU's remain divided and grouped correctly with their Sell Through Rate. Am I perhaps not understanding you properly?

Regards
DBY

Thanks!
Actually I understand your confusion. In that example there was only other column to sort by. I attach a fuller real life example with 2 data sets I use. It's a sample of online listings with overall SKU close % and close % of different ads for a given sku. Since the SKU close percentage is constant for a given sku, sorting the sheet by it still keeps all same skus together, so it looked same as sorting by sku itself (just in different order). However when you sort by Ad close % is when things get scrambled. However I understand why they would.
See attachment.

However your solution is great and exactly what I needed. Thanks!

I will try this out shortly. Thanks Amit!

Hi
Yes I see the problem now. It's difficult, because you have to decide by which value you want to highlight your rows. I haven't tried Amit's solution but the only way I can think of is to perhaps have a Macro to sort and highlight the relevant values, depending on choice.

Regards
DBY