# List unique employees and then all assigned codes in one cell using a formula

#### shobolos

##### Active Member
I have a list of employees with multiple assigned codes. So each employee can have more than one code.

Employee Codes
Employee # 0006 MLUB2621
Employee # 0001 VMVW5126
Employee # 0005 TZSZ3296
Employee # 0004 WQHS5411
Employee # 0005 JILX3602
Employee # 0007 FMLN2422
Employee # 0005 UINI2337
Employee # 0004 VBFR3214
Employee # 0002 OATX4733
Employee # 0008 CVUU5403
Employee # 0009 JMPC2631
Employee # 0006 YPAI4427
Employee # 0002 AIMZ6609
Employee # 0006 SPFW1376
Employee # 0008 SQEN9141
Employee # 0003 JQNN7899
Employee # 0003 QFSO8664
Employee # 0007 EXTN4805
Employee # 0006 FCAA8980
Employee # 0007 UBDI9369

I need list out the unique employees, in a separate part of the spreadsheet, using a formula.

Next to that I need to list all of employees codes in one cell, again using a formula.

Unique Employees All Codes
Employee # 0001 VMVW5126
Employee # 0002 OATX4733, AIMZ6609
Employee # 0003 JQNN7899, QFSO8664
Employee # 0004 WQHS5411, VBFR3214
Employee # 0005 TZSZ3296, JILX3602, UINI2337
Employee # 0006 MLUB2621, YPAI4427, SPFW1376, FCAA8980
Employee # 0007 FMLN2422, EXTN4805, UBDI9369
Employee # 0008 CVUU5403, SQEN9141
Employee # 0009 JMPC2631

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### jumbledore

##### Active Member
I assume your data is in columns A & B.

The first part of your problem which is to get unique entries using a formula can be solved from here: Double Elimination, Getting Unique and Missing Items from Lists | Chandoo.org - Learn Microsoft Excel Online

For the second part, let's suppose your unique emp codes Employee # 0001, Employee # 0002, Employee # 0003, etc are in column E. So just paste the below formula as an array formula (ctrl-shift-enter) in F1 and drag it till you get the desired result:

Code:
``=IFERROR(INDEX(\$B:\$B,SMALL(IF(\$A\$1:\$A\$20=\$E1,ROW(\$A\$1:\$A\$20)),COLUMN()-5)),"")``

#### shobolos

##### Active Member
Many thanks for your response. The link was extremely useful.

With the formula for displaying all of the codes -

- When I drag it down it only shows the 2nd code, if there is one. If there isn't the cell in blank.

- I don't want to drag it across to show the remaining codes as I need all of the relevant codes in one cell, separated by a comma and a space.

I hope this further clarifies my requirements and adds to finding a complete solution.

#### jumbledore

##### Active Member
you have to drag it in the adjacent columns and rows both. you can then concatenate the results

#### shobolos

##### Active Member

Ah, I see I had the formula in the wrong column, hence the missing 1st value.

Is there definitely not a formulated solution to having one formula, as opposed to having lots of formulas and then concatenating them?

The reason being is that I don't know how many codes each employee will have. It could be anything from none to around a hundred.

#### jumbledore

##### Active Member
the best I can come up with is, in cell F1 enter:
Code:
``IF((E1=\$A\$1:\$A\$20),\$B\$1:\$B\$20,"")``
and then drag it down. You then need to press F2 in the respective cell and press F9. You can then copy the array with the codes from the formula bar and will have to manually edit the list to remove the "";

#### AlanY

##### Well-known Member

Excel 2012
ABCDEFGHIJ
1
2Employee # 0006MLUB2621Employee # 0006MLUB2621YPAI4427SPFW1376FCAA8980Employee # 0006 MLUB2621, YPAI4427, SPFW1376, FCAA8980
3Employee # 0001VMVW5126Employee # 0001VMVW5126Employee # 0001 VMVW5126, , ,
4Employee # 0005TZSZ3296Employee # 0005TZSZ3296JILX3602UINI2337Employee # 0005 TZSZ3296, JILX3602, UINI2337,
5Employee # 0004WQHS5411Employee # 0004WQHS5411VBFR3214Employee # 0004 WQHS5411, VBFR3214, ,
6Employee # 0005JILX3602Employee # 0007FMLN2422EXTN4805UBDI9369Employee # 0007 FMLN2422, EXTN4805, UBDI9369,
7Employee # 0007FMLN2422Employee # 0002OATX4733AIMZ6609Employee # 0002 OATX4733, AIMZ6609, ,
8Employee # 0005UINI2337Employee # 0008CVUU5403SQEN9141Employee # 0008 CVUU5403, SQEN9141, ,
9Employee # 0004VBFR3214Employee # 0009JMPC2631Employee # 0009 JMPC2631, , ,
10Employee # 0002OATX4733Employee # 0003JQNN7899QFSO8664Employee # 0003 JQNN7899, QFSO8664, ,
11Employee # 0008CVUU5403
12Employee # 0009JMPC2631
13Employee # 0006YPAI4427
14Employee # 0002AIMZ6609
15Employee # 0006SPFW1376
16Employee # 0008SQEN9141
17Employee # 0003JQNN7899
18Employee # 0003QFSO8664
19Employee # 0007EXTN4805
20Employee # 0006FCAA8980
21Employee # 0007UBDI9369

<tbody>
</tbody>
Sheet2
D2 and copy down

Array Formulas
CellFormula
D2{=INDEX(\$A\$2:\$A\$21, MATCH(0, COUNTIF(\$D\$1:D1, \$A\$2:\$A\$21), 0))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

E2, copy down and across to H10

Array Formulas
CellFormula
E2{=IFERROR(INDEX(\$B\$2:\$B\$21,SMALL(IF(\$A\$2:\$A\$21=\$D2,ROW(\$B\$2:\$B\$21)-ROW(\$D\$2)+1),COLUMNS(\$E2:E2))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Excel 2012
J
2Employee # 0006 MLUB2621, YPAI4427, SPFW1376, FCAA8980

<tbody>
</tbody>
Sheet2

J2 and copy down, almost there, just to cleanup the trailing ,,,
Worksheet Formulas
CellFormula
J2=CONCATENATE(D2," "&E2&", ",F2&", ",G2&", ",H2)

<tbody>
</tbody>

<tbody>
</tbody>

#### AlanY

##### Well-known Member
Excel 2012
J
2Employee # 0006 MLUB2621 ,YPAI4427 ,SPFW1376 ,FCAA8980

<tbody>
</tbody>
Sheet2
J2 and copy down
Worksheet Formulas
CellFormula
J2=CONCATENATE(D2&" ",ConcatenateIf(\$A\$2:\$A\$21,D2,\$B\$2:\$B\$21," ,"))

<tbody>
</tbody>

<tbody>
</tbody>

this UDF borrowed from How to easily concatenate text based on criteria in Excel?

Click Insert > Module, and paste the following code in the Module

Dim i As Integer
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Update 20150414
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

#### shobolos

##### Active Member
My sincere thanks to all that responded to this post.

It would appear that the only around this would be to concatenate the formulas.

Back to the drawing board for me!

#### jumbledore

##### Active Member
My sincere thanks to all that responded to this post.

It would appear that the only around this would be to concatenate the formulas.

Back to the drawing board for me!

Why don't you try the below:

the best I can come up with is, in cell F1 enter:
Code:
``IF((E1=\$A\$1:\$A\$20),\$B\$1:\$B\$20,"")``
and then drag it down. You then need to press F2 in the respective cell and press F9. You can then copy the array with the codes from the formula bar and will have to manually edit the list to remove the "";

1,127,550
Messages
5,625,445
Members
416,106
Latest member
Geo0

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back