Solved

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

Posted on 2016-08-17
5
42 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
  • 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 28

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 28

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 28

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now