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

Posted on 2016-08-17
Medium Priority
Last Modified: 2016-08-17
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.
Question by:nts42a
  • 3

Expert Comment

by:Martin Andrews
ID: 41759716
Here you go.  If you edit the formula, remember to press CTRL + SHIFT + ENTER as it is an array formula!
LVL 35

Accepted Solution

Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41759720
Try this....

In B1

Open in new window

and copy down.
LVL 35

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759725
@ 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.

Author Closing Comment

ID: 41759739
Worked perfectly. Thank you.
LVL 35

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759742
You're welcome. Glad to help.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question