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

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
21
Office Version
365, 2019
Platform
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:

Some videos you may like

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
Joined
Oct 24, 2015
Messages
5,850
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,850
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]
 

Silverhorne

New Member
Joined
Feb 21, 2015
Messages
21
Office Version
365, 2019
Platform
Windows
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
Joined
Oct 24, 2015
Messages
5,850
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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,922
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
Joined
Feb 21, 2015
Messages
21
Office Version
365, 2019
Platform
Windows
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
Joined
Aug 18, 2015
Messages
9,922
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top