Need to number but skip blank rows

[FJ]

Hi, I am trying to number rows in column A using a formula. The numbering
should start with 150, not 1. The problem is that I only want the rows
numbered if the values in column B are unique. When I say “uniqueâ€, any
duplicate entries would be right on top of each other as shown below. The
list is already sorted. Here is an example of what I would like the
numbering to look like:

A B
150 Apples
151 Oranges
Oranges
152 Pears
Pears
Pears
153 Lemons
154 Limes

I’ve played around with formulas using IF and COUNTIF, but so far I haven’t
had much success. I know this is probably simple, but so far I haven’t been
able to figure it out.

Thanks in advance for any help.

 

[Pete_UK]

Put 150 in A1, and then put this formula in A2:

=IF(COUNTIF(B$1:B2,B2)=1,A$1+COUNT(A$1:A1),"")

and copy it down as far as you need to.

Hope this helps.

Pete

 

[NDBC]

You have to put 150 manually in the first cell. Then in a2 put
IF(B3=B2,"",MAX($A$1:A1)+1) and copy it down.
The only diffeence is this puts the number for multiple items in the last
position not first but I'm sure you could modify the idea if this is
critical. This is what you will get

A B
150 Apples
Oranges
151 Oranges
Pears
Pears
152 Pears
153 Lemons
154 Limes

 

[Pete_UK]

Why not compare B2 with B1, and then you could give the OP what s/he
asked for:

=IF(B2=B1,"",MAX($A$1:A1)+1)

Hope this helps.

Pete

 

[FJ]

Hi, Pete, thank you for your response. This was just what I needed.

 

[FJ]

Hi, thanks for your response. I ended up using Pete's formula but yours will
definitely come in handy. Thank you.

 

[FJ]

Hi, thanks for this formula, too. It's interesting, it handles blank cells
in column B differently than your IF/COUNTIF formula, which is the one I
ended up using for my purposes this time. Thanks again!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Hi, Pete, thank you for your response.  This was just what I needed.






- Show quoted text -