?
Solved

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

Posted on 2016-08-17
5
Medium Priority
?
76 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 32

Accepted Solution

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

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

Open in new window

and copy down.
0
 
LVL 32

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 32

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

719 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