# Thread: Extract unique values from multiple columns based on a set criteria to create a running list Thanks: 0 Likes: 0

1. ## Extract unique values from multiple columns based on a set criteria to create a running list

Good day,
I have been working to simplify a log that is used by a lot of people I work with. I originally built it years ago with helper columns and need to get rid of them.
I have 2 items I need to solve but will post the second one in a different post.

Item 1:
I have a massive log, column B has unique items. Columns D-K have the initials of the person that worked on that part of the item.
What needs to happen is the report needs to list the items from the log that match what has been entered in "E1" of the report to "M" of the log.
Any help is greatly appreciated!

A B C D E F G H I J K L M N O
1 Current Helper column Unique Items Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7 Part 8 REPORT Current Helper column
2 Item 1 AA AA AA AA BB BB BB BB AA/BB
3 1 Item 2 BB BB BB BB BB BB BB BB Report 1 BB
4 Item 3 CC DD CC DD CC DD CC DD CC/DD
5 2 Item 4 ABC E ABC E ABC DD E ABC Report 1 ABC/E/DD
6 3 Item 5 FF GG HH II JJ KK LL MMM Report 1 FF/GG/HH/II/JJ/KK/LL/MMM
LOG

Worksheet Formulas
Cell Formula
A2 =IF(\$M2=Report!\$E\$1,1,"")
A3 =IF(\$M3=Report!\$E\$1,MAX(\$A\$2:\$A2)+1,"")
A4 =IF(\$M4=Report!\$E\$1,MAX(\$A\$2:\$A3)+1,"")
A5 =IF(\$M5=Report!\$E\$1,MAX(\$A\$2:\$A4)+1,"")
A6 =IF(\$M6=Report!\$E\$1,MAX(\$A\$2:\$A5)+1,"")
O2 =SUBSTITUTE(TRIM(D2&" "&IF(COUNTIF(\$D2:E2,E2)=1,E2,"")&" "&IF(COUNTIF(\$D2:F2,F2)=1,F2,"")&" "&IF(COUNTIF(\$D2:G2,G2)=1,G2,"")&" "&IF(COUNTIF(\$D2:H2,H2)=1,H2,"")&" "&IF(COUNTIF(\$D2:I2,I2)=1,I2,"")&" "&IF(COUNTIF(\$D2:J2,J2)=1,J2,"")&" "&IF(COUNTIF(\$D2:K2,K2)=1,K2,""))," ","/")
O3 =SUBSTITUTE(TRIM(D3&" "&IF(COUNTIF(\$D3:E3,E3)=1,E3,"")&" "&IF(COUNTIF(\$D3:F3,F3)=1,F3,"")&" "&IF(COUNTIF(\$D3:G3,G3)=1,G3,"")&" "&IF(COUNTIF(\$D3:H3,H3)=1,H3,"")&" "&IF(COUNTIF(\$D3:I3,I3)=1,I3,"")&" "&IF(COUNTIF(\$D3:J3,J3)=1,J3,"")&" "&IF(COUNTIF(\$D3:K3,K3)=1,K3,""))," ","/")
O4 =SUBSTITUTE(TRIM(D4&" "&IF(COUNTIF(\$D4:E4,E4)=1,E4,"")&" "&IF(COUNTIF(\$D4:F4,F4)=1,F4,"")&" "&IF(COUNTIF(\$D4:G4,G4)=1,G4,"")&" "&IF(COUNTIF(\$D4:H4,H4)=1,H4,"")&" "&IF(COUNTIF(\$D4:I4,I4)=1,I4,"")&" "&IF(COUNTIF(\$D4:J4,J4)=1,J4,"")&" "&IF(COUNTIF(\$D4:K4,K4)=1,K4,""))," ","/")
O5 =SUBSTITUTE(TRIM(D5&" "&IF(COUNTIF(\$D5:E5,E5)=1,E5,"")&" "&IF(COUNTIF(\$D5:F5,F5)=1,F5,"")&" "&IF(COUNTIF(\$D5:G5,G5)=1,G5,"")&" "&IF(COUNTIF(\$D5:H5,H5)=1,H5,"")&" "&IF(COUNTIF(\$D5:I5,I5)=1,I5,"")&" "&IF(COUNTIF(\$D5:J5,J5)=1,J5,"")&" "&IF(COUNTIF(\$D5:K5,K5)=1,K5,""))," ","/")
O6 =SUBSTITUTE(TRIM(D6&" "&IF(COUNTIF(\$D6:E6,E6)=1,E6,"")&" "&IF(COUNTIF(\$D6:F6,F6)=1,F6,"")&" "&IF(COUNTIF(\$D6:G6,G6)=1,G6,"")&" "&IF(COUNTIF(\$D6:H6,H6)=1,H6,"")&" "&IF(COUNTIF(\$D6:I6,I6)=1,I6,"")&" "&IF(COUNTIF(\$D6:J6,J6)=1,J6,"")&" "&IF(COUNTIF(\$D6:K6,K6)=1,K6,""))," ","/")

A B C D E
1 Item Parts Report 1
2 Item 2 BB
3 Item 4 ABC/E/DD
4 Item 5 FF/GG/HH/II/JJ/KK/LL/MMM
5
6
Report

Worksheet Formulas
Cell Formula
B2 =IFERROR(INDEX(LOG!B\$2:B\$6,AGGREGATE(15,6,(ROW(LOG!\$B\$2:\$B\$6)-ROW(LOG!\$B\$2)+1)/(LOG!\$M\$2:\$M\$6=\$E\$1),ROWS(\$A\$1:\$A1))),"")
C2 =IF(ISNA(INDEX(LOG!B2:O6,MATCH(B2,LOG!B2:B6,0),14)),"",INDEX(LOG!B2:O6,MATCH(B2,LOG!B2:B6,0),14))
B3 =IFERROR(INDEX(LOG!B\$2:B\$6,AGGREGATE(15,6,(ROW(LOG!\$B\$2:\$B\$6)-ROW(LOG!\$B\$2)+1)/(LOG!\$M\$2:\$M\$6=\$E\$1),ROWS(\$A\$1:\$A2))),"")
C3 =IF(ISNA(INDEX(LOG!B3:O7,MATCH(B3,LOG!B3:B7,0),14)),"",INDEX(LOG!B3:O7,MATCH(B3,LOG!B3:B7,0),14))
B4 =IFERROR(INDEX(LOG!B\$2:B\$6,AGGREGATE(15,6,(ROW(LOG!\$B\$2:\$B\$6)-ROW(LOG!\$B\$2)+1)/(LOG!\$M\$2:\$M\$6=\$E\$1),ROWS(\$A\$1:\$A3))),"")
C4 =IF(ISNA(INDEX(LOG!B4:O8,MATCH(B4,LOG!B4:B8,0),14)),"",INDEX(LOG!B4:O8,MATCH(B4,LOG!B4:B8,0),14))
B5 =IFERROR(INDEX(LOG!B\$2:B\$6,AGGREGATE(15,6,(ROW(LOG!\$B\$2:\$B\$6)-ROW(LOG!\$B\$2)+1)/(LOG!\$M\$2:\$M\$6=\$E\$1),ROWS(\$A\$1:\$A4))),"")
C5 =IF(ISNA(INDEX(LOG!B5:O9,MATCH(B5,LOG!B5:B9,0),14)),"",INDEX(LOG!B5:O9,MATCH(B5,LOG!B5:B9,0),14))
B6 =IFERROR(INDEX(LOG!B\$2:B\$6,AGGREGATE(15,6,(ROW(LOG!\$B\$2:\$B\$6)-ROW(LOG!\$B\$2)+1)/(LOG!\$M\$2:\$M\$6=\$E\$1),ROWS(\$A\$1:\$A5))),"")
C6 =IF(ISNA(INDEX(LOG!B6:O10,MATCH(B6,LOG!B6:B10,0),14)),"",INDEX(LOG!B6:O10,MATCH(B6,LOG!B6:B10,0),14))

2. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

something like this?

 Unique Items Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7 Part 8 Unique Items Part Item 1 AA AA AA AA BB BB BB BB Item 1 AA/BB Item 2 BB BB BB BB BB BB BB BB Item 2 BB Item 3 CC DD CC DD CC DD CC DD Item 3 CC/DD Item 4 ABC E ABC E ABC DD E ABC Item 4 ABC/E/DD Item 5 FF GG HH II JJ KK LL MMM Item 5 FF/GG/HH/II/JJ/KK/LL/MMM

Code:
```// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Unique Items"}, "Attribute", "Value"),
Group = Table.Group(Unpivot, {"Unique Items"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Part", each List.Distinct(Table.Column([Count],"Value"))),
Extract = Table.TransformColumns(List, {"Part", each Text.Combine(List.Transform(_, Text.From), "/"), type text})
in
Extract```
I don't understand your Report option

3. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

or like this:

 Unique Items Parts Report Item 2 BB Report1 Item 4 ABC/E/DD Report1 Item 5 FF/GG/HH/II/JJ/KK/LL/MMM Report1

Code:
```// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Unique Items", "Report"}, "Attribute", "Value"),
Filter = Table.SelectRows(Unpivot, each ([Report] = "Report1")),
Group = Table.Group(Filter, {"Unique Items"}, {{"Count", each _, type table}}),
ListParts = Table.AddColumn(Group, "Parts", each List.Distinct(Table.Column([Count],"Value"))),
ExtractParts = Table.TransformColumns(ListParts, {"Parts", each Text.Combine(List.Transform(_, Text.From), "/"), type text}),
ListReport = Table.AddColumn(ExtractParts, "Report", each List.Distinct(Table.Column([Count],"Report"))),
ExtractReport = Table.TransformColumns(ListReport, {"Report", each Text.Combine(List.Transform(_, Text.From)), type text})
in
ExtractReport```

4. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

I don't want to use VBA, for the simple fact that I don't know how(I should learn).
The report is on a separate sheet from the log, I would enter "Report 1" into "E1" (or what ever report it may be) and it would look for this in column "M" of the log then return a unique list of initials separated by "/".
The first helper column is now redundant, as the index/aggregate formula in column "B" replaces the old lookup I used to have in there.

5. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

This is not vba but PowerQuery M-language

You can put the result table anywhere and add/remove the Report filter, eg.

 Unique Items Part 1 Part 2 Part 3 Part 4 Part 5 Part 6 Part 7 Part 8 Report Unique Items Parts Report Item 1 AA AA AA AA BB BB BB BB Report2 Item 1 AA/BB Report2 Item 2 BB BB BB BB BB BB BB BB Report1 Item 2 BB Report1 Item 3 CC DD CC DD CC DD CC DD Item 4 ABC/E/DD Report1 Item 4 ABC E ABC E ABC DD E ABC Report1 Item 5 FF/GG/HH/II/JJ/KK/LL/MMM Report1 Item 5 FF GG HH II JJ KK LL MMM Report1

edit:
but it's ok if you prefer many formulas
Have a nice day

6. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

Your B2 formula on the Report sheet is fine as is. For the C2 formula, you can use:

=IFERROR(TEXTJOIN("/",1,IF(INDEX(Log!\$D\$2:\$K\$6,MATCH(B2,Log!\$B\$2:\$B\$6,0),0)<>"",IF(MATCH(INDEX(Log!\$D\$2:\$K\$6,MATCH(B2,Log!\$B\$2:\$B\$6,0),0),INDEX(Log!\$D\$2:\$K\$6,MATCH(B2,Log!\$B\$2:\$B\$6,0),0),0)=COLUMN(Lo g!\$D\$2:\$K\$2)-COLUMN(Log!\$D\$2)+1,INDEX(Log!\$D\$2:\$K\$6,MATCH(B2,Log!\$B\$2:\$B\$6,0),0),""),"")),"")

and confirm it by pressing Control+Shift+Enter. No formulas required on the LOG sheet at all. Note that this requires the TEXTJOIN function available in Excel 365. Also note that sometime within the next year or so, Microsoft will release some new functions, including UNIQUE which would make it much simpler. These would also be available in Excel 365.

7. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

Thank you so much Eric W, that worked perfectly! I am using Excel 365 and have a few other places I've used the textjoin function already.
I do use excel every day but am seeing my knowledge is very basic outside of combing functions in the formula bar.

8. ## Re: Extract unique values from multiple columns based on a set criteria to create a running list

If you want to try some VBA, follow these instructions:

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. On the window that opens, paste this code:

Code:
```Public Function GetInits(ItemName As String, Log As Range)
Dim MyDict As Object, MyVals As Variant, i As Long, j As Long

Set MyDict = CreateObject("Scripting.Dictionary")
MyVals = Log.Value
For i = 1 To UBound(MyVals)
If MyVals(i, 1) = ItemName Then
For j = 3 To UBound(MyVals, 2)
MyDict(MyVals(i, j)) = 1
Next j
Exit For
End If
Next i
GetInits = Join(MyDict.keys, "/")

End Function```
Press Alt-Q to close the editor. Now in place of that long C2 formula, use:

=getinits(B2,Log!\$B\$2:\$K\$6)

Either way you go, I'm glad it works for you.