Excel assign auto number for text

Question

I have a spreadsheet that I need a formula to create what would act similar to an auto number if a value is a duplicate based on a Countif formula in another column. I want to make an auto # for every name that has multiple order#. So in this example Joe is getting ID of 1 as he was first is sheet and Bunny gets 2 and when Joe's name repeats it is still 1. This names are could be dozens of people and will be different each file. Thanks for help.

ORDER#  LN  QTY ITEM        LOC         NAME          Countifs  Auto Number
OD10560 1   24  part#12     Location 1  Joe John       1            1
OD10559 1   1   part#114    Location 2  Bunny Johnson  2            2
OD10569 1   3   part#444    Location 1  Joe John       1            1
OD10567 1   3   part#777    Location 2  Archy Bunker   0    
OD10567 2   4   part#123    Location 2  Archy Bunker   0    
OD10562 1   24  part#458    Location 1  Kay Harrison   0    
OD10577 1   2   part#471    Location 2  Bunny Johnson  1            2
OD10577 2   1   part#736    Location 2  Bunny Johnson  1            2
OD10563 1   2   part#778    Location 1  Mark Riddle    0    
OD10579 1   2   part#3347   Location 1  Terry Clark    0    

Spreadsheet is here for formula reference, Link

Answer 1

Assuming the countifs column is column H, and the name column is column G, put this in I2 and drag down:

=if(H2>0,iferror(index(I$1:I1,match(G2,G$1:G1,0)),max(I$1:I1)+1),"")

This first checks to see if there are duplicate values for that name, as defined in the countifs column. If there are, it checks to see if that particular name has been shown on a row above on this tab yet, using the MATCH function. If there is a match, it gives the row number of that match. Note that the references start with an absolute reference to row 1, and a relative reference to row 1. This will drag the bottom row of the testing area as you drag the formula down. The formula will always end at the cell above the formula cell.

If the MATCH number provides the row number, it gives that value to the INDEX function, which pulls in the already-assigned number found above on column I. If there is an error, there is no match yet, so it takes the MAX value found in the column thus far above, and adds 1. If THAT provides an error, see below.

Depending on the value of I1, you may need to hardcode the value of the first match as '1', or else change the formula to the following:

=if(H2>0,iferror(index(I$1:I1,match(G2,G$1:G1,0)),iferror(max(I$1:I1)+1,1)),"")

The only difference here is that if the MAX function returns an error, then there are no number values above yet, and therefore it is presumed to be the first hit, which defaults to 1.