Need to number each occurrence of primary ID # in same column in Excel

I have an Excel file with over 11,000 rows of data. Column A (See Exemplar File) is the primary identifier ID number. The problem is that there are multiples of many of this primary ID due to use in multiple locations (see column I & J.) I need a formula or method to number the 1st, 2nd, 3rd, etc. occurrence of each primary ID number (see column B.) This way I can sort & filter the file so only the 1st of each ID number is showing. I ultimately need to perform a vlookup from another data set where all the primary ID’s are combined. I want to only pull in the data to the first occurrence of each primary ID number. I greatly appreciate your assistance.
Exemplar-File-08.17.16.xlsx
nts42aAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Try this....

In B1
=COUNTIF(A$2:A2,A2)

Open in new window

and copy down.
0
 
Martin AndrewsCommented:
Here you go.  If you edit the formula, remember to press CTRL + SHIFT + ENTER as it is an array formula!
Exemplar-File-08.17.16_v2.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@ Martin

The formula you suggested is not an Array Formula but a regular formula so you don't need to confirm that formula with Ctrl+Shift+Enter. Only Enter is sufficient.
0
 
nts42aAuthor Commented:
Worked perfectly. Thank you.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.