How do you auto number?

xcentric · #1 04-30-2007, 12:15 AM

I want to auto number a spreadsheet but I can't seem to get the formula right. I want to number the rows based on the neighboring cells. If the first name and last name match then number it the same then move on. Can someone help me in the right direction please? I would like the numbering to look like this:

1 John Doe
1 John Doe
2 Jane Doe
3 Frank Johnson
4 Ralph Smith
4 Ralph Smith
4 Ralph Smith

BigBas · #2 04-30-2007, 01:36 AM

This is one way to do it:

I put a number 1 in the first row. Then in cell A2 (this is assuming the numbers are in column A, and names in B):

Code:

=IF(B2="","",IF(COUNTIF($B$1:B2,B2)>1,INDEX($A$1:A1,MATCH(B2,$B$1:$B$100,0)),MAX($A$1:A1)+1))

Try that and let me know if it works.

EDIT to add: You can fill that formula down as far as necessary. If there is no name in the corresponding cell in column B, it will return blank.

olasa · #3 04-30-2007, 04:58 AM

If the names are entered in identical ways, meaning that John Doe is not written John, Doe or Doe, John, the below can be used.

I let the names start in A1, so I put 1 in B1.
Then I put =B1--(A1<>A2) in B2, and copy the formula downward.
Done

//Ola

Code:

John Doe           1                  -->1
John Doe           =B1--(A1<>A2)       -->1
Jane Doe           =B2--(A2<>A3)       -->2
Frank Johnson     ....                       
Ralph Smith
Ralph Smith
Ralph Smith

BigBas · #4 04-30-2007, 10:25 AM

Quote:

Originally Posted by olasa

If the names are entered in identical ways, meaning that John Doe is not written John, Doe or Doe, John, the below can be used.

I let the names start in A1, so I put 1 in B1.
Then I put =B1--(A1<>A2) in B2, and copy the formula downward.
Done

//Ola

Code:

John Doe           1                  -->1
John Doe           =B1--(A1<>A2)       -->1
Jane Doe           =B2--(A2<>A3)       -->2
Frank Johnson     ....                       
Ralph Smith
Ralph Smith
Ralph Smith

Olasa.... Interesting formula; however, I don't think that would work if the names were out of order. For example, if John Doe was added again after Ralph Smith, it would assing him a brand new number. Is there a way around that using your setup.

olasa · #5 04-30-2007, 09:18 PM

Thanks BigBas, yea well they way xcentric presented the problem, I assumed he would sort the list, which would lead to no blanks. And it would help him keep a "consistancy"; 'John Doe' would not become 'John, Doe' or 'John Doe_'.

xcentric has to answer but if the first assumption is wrong I would stick with your proposal* and if the second assumption is wrong I'd use the method of first clear all spaces and commas.

But if the inconsistancy is - call it - 'level three difficulty', meaning 'John Doe could also be 'Doe, John ', the pattern recognition would have to be steped up bit more.

But all in all, let's hope that one of the proposed options suit xcentric, and if not, we'll see if we can find another way.

//Ola

...This line of resoning might help xcentric to forsee some future problems

* ...BigBas I just read you last question, could my setup be used if John Doe was added at the end...sure if the formula (in B2) is changed to: =B1+MIN(--(A2<>$A$1:A1)) and entered as an 'array formula';
xcentric: type the formula but don't hit enter. Instead hold down Ctrl and Shift and then hit Enter. If Excel now has added two squiggly brackets { } around the formula, it should work.

**mikerickson** · #6 04-30-2007, 09:25 PM

in A2

=IF(B2=B1,A1,A1+1)

works for me.

BigBas · #7 04-30-2007, 11:42 PM

Quote:

Originally Posted by olasa

* ...BigBas I just read you last question, could my setup be used if John Doe was added at the end...sure if the formula (in B2) is changed to: =B1+MIN(--(A2<>$A$1:A1)) and entered as an 'array formula';
xcentric: type the formula but don't hit enter. Instead hold down Ctrl and Shift and then hit Enter. If Excel now has added two squiggly brackets { } around the formula, it should work.

Ola... Your strength and understanding of the array formulas never ceases to amaze me. Nevertheless, your formula did not work for me (unless either we have a misunderstanding or I have done something wrong). Assuming the list is NOT sorted, let's use the data you have previously supplied. If John Doe repeats at the end of the list, out of order with the other John Doe, it does not assume the same number as previous John Doe. It is assigned the same number as the last entry. Does that make sense?

olasa · #8 05-01-2007, 05:52 PM

Thanks for the compliment BigBas, but I missed your point - that all John Doe should have the same number. I can hardly blame the IRS and late night for diverting my focus (it's due tomorrow here). So to do what your meant, I would basically use the same formula.

//Ola

Code:

=IF(COUNTIF($A$1:A1,A2)=0,MAX($B$1:B1)+1,VLOOKUP(A2,$A$1:B1,2,0))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)