Solved

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

Posted on 2016-08-17
5
67 Views
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.
Exemplar-File-08.17.16.xlsx
0
Comment
Question by:nts42a
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 2

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!
Exemplar-File-08.17.16_v2.xlsx
0
 
LVL 31

Accepted Solution

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

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

Open in new window

and copy down.
0
 
LVL 31

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.
0
 

Author Closing Comment

by:nts42a
ID: 41759739
Worked perfectly. Thank you.
0
 
LVL 31

Expert Comment

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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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