numbering rows/skipping rows with repeated entries

pairustwo

New Member
Joined
Sep 19, 2002
Messages
6
Hi All
I want to make a simple linear series fill, starting at 1, in column A.
The problem is that in column B, where I have a LONG list of names I'd like numbered, many of the names appear more than one time in a row and I want only one number assigned to a name; in the end I have the number of unique people who appear on the list and not the number of entries.

All people with mulitiple entries appear in contiguous rows, so John Smith is listed in B1 - B3 and Jane Doe is listed in B4 - B5. To clarify I am hoping that in A1(to the left of John Smith's first entry) will be labled "1" and A4 (to the left of Jane Doe's first entry) will be labled "2" while all other cells in the A column are left blank.

Thanks for your help
pairus
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Insert a row before your data and in A2 enter & copy doen:

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

See...
Book4
ABCD
1#Names
21n1
3 n1
42n2
5 n2
6 n2
7
Sheet1
 
Upvote 0
Thanks for the quick reply.

Could you break down the syntax for me.

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

I'm a beginner here.

>Insert a row before your data and in A2 >enter & copy doen:

Does this assume that my "names" are in clolumn B? and starting in row 2?
Will this work in all rows 1 - 800?

Thanks
parius
This message was edited by pairustwo on 2002-09-21 10:32
 
Upvote 0
On 2002-09-21 10:31, pairustwo wrote:
Thanks for the quick reply.

Could you break down the syntax for me.

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

I'm a beginner here.

>Insert a row before your data and in A2 >enter & copy doen:

Does this assume that my "names" are in clolumn B? and starting in row 2?
Will this work in all rows 1 - 800?

Thanks
parius
This message was edited by pairustwo on 2002-09-21 10:32

Names are in column B starting at row 2, a setup that enables the formula to work.

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

COUNTIF as the condition of IF looks at whether a name in B already exists in the range right above that name. If so, we get "", otherwise we add 1 to the index of the most recently numbered unique name.
 
Upvote 0
WELCOME TO THE BOARD!

There seems to be something missing in the formula. I'll break the formula out as best as I can and show you where the missing data is.

COUNTIF($B$1:B1,B2)--This means count only the values that equal B2 in the array $B$1:B1.

MAX($A$1:A1)--This gives you the highest value found in the array $A$1:A1.

THIS IS WHERE YOU ARE MISSING DATA. When performing an IF statement, you must indicate what to do if the IF statement is TRUE and what to do if it is FALSE. COUNTIF($B$1:B1,B2) does not have any conditions to check. This will only yield a number. You should have something like:

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

To read the entire formula from left to right, it means this:

Look at the values in the array of $B$1:B1 and count ONLY the values that equal B2. If the count equals 0, then keep the cell blank. Otherwise, look in the array of $A$1:A1 and find the maximum value within that array and add 1 to it.

Does this help?
 
Upvote 0
OK so one final question.
Where do I enter this formula?
Can I / Do I select an entire column and enter a formula, or both column A and B?

Looking at Aladin's inital post it looks as though the formula is different on each cell in Column A. If this is the case it is much more difficult than entering the numbers by hand - which is what I am trying to avoid in the first place.


Thanks you guys.

pairus
This message was edited by pairustwo on 2002-09-21 15:56
 
Upvote 0
On 2002-09-21 18:44, phantom1975 wrote:
Put the formula in cell B1. Then copy and paste the cell down the entire column.

Hi Phantom1975:

There is a problem with your formulation because ... if the name entries are in cells B2 ... down to b6 or whatever, then

1. The formula is to be entered in cells of column A, and not B

2. For the correct formula to be entered in cell A2 ... see the formula in Aladin's post above -- it is this formula that has to be then copied down to cells A3 ... to whatever!

You may want to try the two steps above to verify that it really gives the correct results.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-09-21 19:18
 
Upvote 0
I entered this Formula-
=IF(COUNTIF($B$1:B1,B2),"",MAX($A$1:A1)+1)
into A2 and then again into A3, A4, and so on. The results are that I get a 1 next to each name in B column. 1's all the way down.

Again I think than Aladan's formula needs to be changed for each row - see his original post. Am I wrong here?

OK guys feel free to let this one go if you'd like because I'm feeling like I need to go read an excel book instead of the quick and dirty - which was to ask you all.

Thanks for your help
Pairus
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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