Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Feb 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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))
    Last edited by Silverhorne; Jul 8th, 2019 at 06:00 PM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,863
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,863
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  4. #4
    New Member
    Join Date
    Feb 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,863
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool 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
    Last edited by sandy666; Jul 8th, 2019 at 06:35 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default 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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    New Member
    Join Date
    Feb 2015
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

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

    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.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •