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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

pairustwo

New Member
Joined
Sep 19, 2002
Messages
6
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962

ADVERTISEMENT

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?
 

pairustwo

New Member
Joined
Sep 19, 2002
Messages
6

ADVERTISEMENT

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
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Put the formula in cell B1. Then copy and paste the cell down the entire column.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

pairustwo

New Member
Joined
Sep 19, 2002
Messages
6
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
 

Forum statistics

Threads
1,144,329
Messages
5,723,733
Members
422,512
Latest member
MHau5

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
Top