Display Multiple Results for Same Matching Key

[AndromedusO]

I currently have a table that contains identification numbers of people in the first column, their position title in the second, name in the third, and some other admninistrative data in following columns.

Most identification numbers are unique, but some match each other (they aren't technically ID numbers but more like specific job/role ID numbers).

What I need is a function that will look at the first column for any one of a specific set of four different ID numbers, and then return the first matching ID number in the first cell, the corresponding position title in the second cell, corresponding name in the third, etc. Then in the following row, do the same thing again but skip the reference that it just found in the row above it. Keep in mind that this is likely to be second occurance of that same reference ID number. The reference table data will change from time to time and this output must be robust enough to change with it.

Using Excel 2007.

Thanks for any help!

[Pepe Le Mokko]

Hi,
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[AndromedusO]

In the sample I have attached, the left side is the data and the right side is the output. As you can see, there are a couple of ID's "SEQPOS" that I want to gather and filter all the other unique ID's out. I have made the workbook much smaller than reality.

Edit: I made a mistake and did not format the cells "general," please do so when you open them so they display correctly.

[Pepe Le Mokko]

I think you can find the solution here

[AndromedusO]

The problem is that the data isn't necessarily duplicate. Sometimes it will be the first and only time that the key I am searching for, such as 9991, occurs.

[AndromedusO]

I found a solution. Using the countif function in a column for each key, and then summing the countif columns in a new column, created a unique number each time one of those keys occured. I then was able to use this column as a reference for vlookup. Thanks for the help! I wish there was a better function for this but if there is then it's beyond me.

[micope21]

Hi

Try this Cell L6

Please Login or Register  to view this content.

copy down

Cell M6

Please Login or Register  to view this content.

This is array formula!! Press same time Ctrl+Shift+Enter, NOT ENTER?? Then copy down and cross.

Regard
micope21