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

Silverhorne

New Member
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

<tbody>
</tbody>
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,""))," ","/")

<tbody>
</tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>
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))

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

sandy666

Well-known Member
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:
``````[SIZE=1]// 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[/SIZE]``````
I don't understand your Report option

sandy666

Well-known Member
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:
``````[SIZE=1]// 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[/SIZE]``````

Silverhorne

New Member
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.

sandy666

Well-known Member
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

Last edited:

Eric W

MrExcel MVP
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(Log!\$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.

Silverhorne

New Member
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.

Eric W

MrExcel MVP

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.

1,102,782
Messages
5,488,850
Members
407,658
Latest member
Arias610

This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...