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

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
42
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
something like this?

Unique ItemsPart 1Part 2Part 3Part 4Part 5Part 6Part 7Part 8Unique ItemsPart
Item 1AAAAAAAABBBBBBBBItem 1AA/BB
Item 2BBBBBBBBBBBBBBBBItem 2BB
Item 3CCDDCCDDCCDDCCDDItem 3CC/DD
Item 4ABCEABCEABCDDEABCItem 4ABC/E/DD
Item 5FFGGHHIIJJKKLLMMMItem 5FF/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
 
Upvote 0
or like this:

Unique ItemsPartsReport
Item 2BBReport1
Item 4ABC/E/DDReport1
Item 5FF/GG/HH/II/JJ/KK/LL/MMMReport1

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]
 
Upvote 0
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.
 
Upvote 0
This is not vba but PowerQuery M-language

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

Unique ItemsPart 1Part 2Part 3Part 4Part 5Part 6Part 7Part 8ReportUnique ItemsPartsReport
Item 1AAAAAAAABBBBBBBBReport2Item 1AA/BBReport2
Item 2BBBBBBBBBBBBBBBBReport1Item 2BBReport1
Item 3CCDDCCDDCCDDCCDDItem 4ABC/E/DDReport1
Item 4ABCEABCEABCDDEABCReport1Item 5FF/GG/HH/II/JJ/KK/LL/MMMReport1
Item 5FFGGHHIIJJKKLLMMMReport1

edit:
but it's ok if you prefer many formulas :)
Have a nice day
 
Last edited:
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
Glad to help!

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.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top