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

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274
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
 

Some videos you may like

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
Joined
Jan 17, 2014
Messages
259
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
Joined
Sep 7, 2010
Messages
274
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
Joined
Jan 17, 2014
Messages
259
you have to drag it in the adjacent columns and rows both. you can then concatenate the results
 

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274

ADVERTISEMENT

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
Joined
Jan 17, 2014
Messages
259
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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Sep 7, 2010
Messages
274
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
Joined
Jan 17, 2014
Messages
259
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 "";
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top