# numbering rows/skipping rows with repeated entries

#### pairustwo

##### New Member
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.

pairus

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

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

On 2002-09-21 10:31, pairustwo wrote:

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.

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?

[...]
There seems to be something missing in the formula. [...]

Really? Did you try it?

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

Put the formula in cell B1. Then copy and paste the cell down the entire column.

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

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.

Pairus

Replies
2
Views
346
Replies
1
Views
234
Replies
8
Views
247
Replies
1
Views
103
Replies
0
Views
367

1,218,692
Messages
6,143,944
Members
450,517
Latest member
Rovex

### 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.

### Which adblocker are you using?

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

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