Locating first occurences of items

[Marvick1]

Hi,

You guys all helped me out 2 years ago with a problem I thought impossible to solve but which elevated me to miracle worker in the office!

I have another problem - I have a long, long list of customers & booking reference numbers (I work for a holiday company). I need to identify & highlight the first occurences of each customer & reference number in the list. The customer/reference might appear 5, 10, 20 times or more in the list but I just want the first occurence highlighted.

Is there any easy way to do this? Conditional formatting?

Any help appreciated!!!

Cheers,
Martin.................

 

[doofusboy]

You could use a macro that sorts by customer/reference and then starts at the top, highlights that cell and move down the list highlighting each cell that is different than the cell above it.

 

[pagrender]

Conditional formatting will work

Use this formula in the conditional formula box:
=COUNTIF($A$1:$A1,A1)=1

Drag the format down and it should work - at least it did when I tested it!

 

[Marvick1]

Now THAT looks pretty good to me!!

Many thanks & have a good day.

Regards,
Martin.....

 

[pagrender]

Thanks for the feedback!

 

[Marvick1]

All looking good, my reputation as a miracle worker intact!

One quick addition though - the conditional formatting works fine for Column A. Column B, meanwhile, features the dates my customers went on their holiday. Can conditional formatting help whereby if Cell A1 has been identified as the first occurence of an item (& highlighted using the solution already provided), Cell B1 then automatically highlights? And if B1 has been highlighted, could conditional formatting trigger cell C1 to be highlighted?

Or is that asking too much of Excel?

Again, any & all advice appreciated!

 

[Weaver]

A slight mod

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

Rather than just setting this condition on column 1, set it on all the columns you wish to format.

 

[Marvick1]

Many thanks for the quick reply - that works but I might need to quantify what I'm trying to do (& which I think might be asking too much of Excel). Here's some dummy data:

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=319 border=0 x:str><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=82 height=35>BOOKING REF</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 108pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=144>LEAD NAME</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 70pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=93>Date of Travel</TD></TR><TR style="HEIGHT: 38.25pt; mso-yfti-irow: 24" height=51><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; HEIGHT: 38.25pt; mso-ignore: style; mso-pattern: auto none" width=82 height=51 x:num>193025</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; mso-ignore: style; mso-pattern: auto none" width=144>SMITH, MRS S</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; WIDTH: 70pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93 x:num="40878">01-Dec-2011</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 62pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=82 height=51 x:num>193025</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; WIDTH: 108pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=144>SMITH, MRS S</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; WIDTH: 70pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93 x:num="40878">01-Dec-2011</TD></TR><TR style="HEIGHT: 38.25pt; mso-yfti-irow: 2" height=51><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; HEIGHT: 38.25pt; mso-ignore: style; mso-pattern: auto none" width=82 height=51 x:num>263158</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 0.5pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; mso-ignore: style; mso-pattern: auto none" width=144>ALLEN, MISS T</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; WIDTH: 70pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93 x:num="40878">01-Dec-2011</TD></TR><TR style="HEIGHT: 38.25pt; mso-yfti-irow: 4" height=51><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; HEIGHT: 38.25pt; mso-ignore: style; mso-pattern: auto none" width=82 height=51 x:num>273655</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BACKGROUND: #ffff99; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 0.5pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-STYLE: italic; mso-ignore: style; mso-pattern: auto none" width=144>BUNDY, MR A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext; WIDTH: 70pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93 x:num="40878">01-Dec-2011</TD></TR></TBODY></TABLE>

What I'd LIKE is for Cell C1 to highlight (as A1 & B1 have been conditionally formatted), but not C2.

Any advice welcome!!!!

 

[Weaver]

My advice would be to read my post and follow what I said, as this does exactly what you outline.

1. Select all the cells in the range, starting from the top left.
2. In condtional formatting, choose 'formula is'
3. in the formula box

=COUNTIF($A$xx:$Axx,$Axx)=1

replacing xx with the row number of the first row in the selection

4. Choose your format.
5. Assert your position as office miracle worker