Thread: How do you auto number?

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.

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

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.

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.

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?

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))

But my requirement is little different, please let me know how to do that.
Here is the example
Below Serial number column should come automatically and re-initialized when column A (Name) get changed.
--
Name Pay rate Serial no
John 12 1
John 13 2
John 14 3
Mike 10 1
Mike 12 2
Mike 14 3
Mike 16 4
Sam 10 1
Sam 11 2
--

Thanks
Sam