CODEWITHSUNDEEP
excel-formula
user4907546
user4907546

Reputation: 121

Return second (and subsequent) unique name in list

I have a list of data that I get from a third party. For simplicity, lets say that Column A is the Unique ID (alpha-numeric), and Column B is the employee who is assigned to that ID. One employee has several ID's, as they work several cases at a time. A few of the Unique ID's begin with "AC", and these ID's are special cases.

I need a formula that will search through Column A on the "Raw Data" sheet for any license number that begins with "AC", and return the Assigned Employee name on my "Assigned Employees" sheet. This is easy enough for the first one with a simple index match formula. However, I need it to bring back the second name, and any other names that are there. In the example below, I would need it to bring back Paul, then Lee. 

Column A     Column B  
Unique ID    Assigned Employee  
AC798358     Paul  
90807248     Paul  
AC48298      Lee  
B98281       Lee  
AC42795      Lee

The table on "Assigned Employees" looks like this: 

Employee 1     Employee 2     Employee 3     Employee 4
Paul           Lee

I'm using this index match formula to get the first return (Paul), but it will only work for the first "AC" ID number on the sheet.

=INDEX('Assigned'!$B:$B,MATCH("AC*",'Assigned'!$A:$A,0))

I'm trying this formula, which would bring the first and subsequent returns by changing the "k" number for the "Small" function, but it's not working for me.

=INDEX('Assigned'!$B:$B,SMALL(IF('Assigned'!$A:$A="AC*",ROW('Assigned'!$A:$A)-ROW(INDEX('Assigned'!$A:$A,1,1))+1),1))

I know that it doesn't like this part: IF('Assigned'!$A:$A="AC*", but I don't know how else to write it to make it work. Any help would be appreciated.

Possibly relevant: there are a lot of blank rows in this data set.

Upvotes: 0

Views: 293

Answers (1)

user11060139
user11060139

Reputation:

There is a standard array formula method for pulling a unique list from a list of duplicates. For your sample data, put this in D2, finish with ctrl+shift+enter (aka CSE) then drag right.

=INDEX($B2:$B10, MATCH(0, COUNTIF($C2:C2, $B2:$B10), 0))

You can add conditions (e.g. IF(LEFT($A2:$A10, 2)="AC") to this.

=INDEX($B2:$B10, MATCH(0, IF(LEFT($A2:$A10, 2)="AC", COUNTIF($C2:C2, $B2:$B10)), 0))

This style of LISTUNIQUE formula requires an unused cell to the left (or above if listing into rows). If you don't have the room for an unused cell to the left or above, you could avoid that by using a more conventional formula to achieve the first item in the list and modifying the second to use the first as its reference starting point.

'in D2
=INDEX(B2:B10, MATCH("AC*", A2:A10, 0))
'in E2 (with CSE and dragged right)
=INDEX($B2:$B10, MATCH(0, IF(LEFT($A2:$A10, 2)="AC", COUNTIF($D2:D2, $B2:$B10)), 0))

enter image description here

You can avoid the #N/A errors when you run out of matching items with a wrapping IFERROR function.

Upvotes: 1

Related Questions