+ Reply to Thread
Results 1 to 10 of 10

Help extracting unique combinations from two columns with criteria that ignores blanks

  1. #1
    Registered User
    Join Date
    05-14-2018
    Location
    Seattle, Washington
    MS-Off Ver
    2016
    Posts
    11

    Help extracting unique combinations from two columns with criteria that ignores blanks

    I've been working on a formula to extract unique combinations from two columns using a helper column. However, I've been unsuccessful in applying criteria to this. I'm using the following array formula:

    =IFERROR(IF(COUNTA(Master!$B$2:B2)>MAX(Master!$D$2:$D$16),"",INDEX(Master!$B$2:$B$16,MATCH($A$1&COUNTA(Master!$B$2:B2),Master!$F$2:$F$16&Master!$D$2:$D$16,0))),"")

    The criteria is simply the name of the client which is populated into A1, and I've got a separate sheet for each client.

    The idea is that I want to be able to enter the data into the master table and then have it output to each sheet based on the client's name, and this formula functions to split the data up by season and department.

    This formula works as expected, except that it creates blank cells when a client doesn't match one of the combinations of season and department. For example, if one client doesn't have any data for the first unique combination of season and department, then their first row in the table will just be blank.

    I'd like to not show a blank, and instead skip that row for that client altogether. The way it is now is very hard to manage with larger data sets with multiple clients.

    I've attached a dummy worksheet. Could somebody take a look and help me out here? I generally understand why my criteria isn't working, but I don't quite understand how to fix it.
    Attached Files Attached Files

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    your example has mixed columns. what is your real result?
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    05-14-2018
    Location
    Seattle, Washington
    MS-Off Ver
    2016
    Posts
    11

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    What do you mean by "mixed columns?"

    The results that I'm seeing is that Seasons and Department are output only to the proper client(which is good), but blanks show up when a client doesn't match any unique combination in the master table.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    here are

    A
    B
    11
    Competition Type
    12
    2018/2019 Season
    13
    2019/2020 Season
    14
    Hardware Department
    15
    Media Department
    16
    Department


    on master tab these are separated

    and which table is correct? blue or black?

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    maybe this one will be ok
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-14-2018
    Location
    Seattle, Washington
    MS-Off Ver
    2016
    Posts
    11

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    Okay, my mistake. The formula I'm having trouble with is for the first table in each tab. The second table functions as I want it to. Sorry, I should have left the second table out.

  7. #7
    Registered User
    Join Date
    05-14-2018
    Location
    Seattle, Washington
    MS-Off Ver
    2016
    Posts
    11

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    That's not quite what I was wanting to achieve. I want the first table(blue) to extract the "Season" and "Department" columns from the Master tab into each tab I've created for each client. So far, the only part of that that doesn't work is blanks appear when a client's name doesn't match the unique combination of "season" and "department."

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    ok here is corrected file

    and example code for Bremerton table (automatically generated so you don't need do it manually and no, this is not vba but PowerQuery aka Get&Transform)
    Please Login or Register  to view this content.
    ---
    also you can do that with a PivotTable with filter on each client
    Attached Files Attached Files
    Last edited by sandy666; 05-21-2018 at 07:12 PM. Reason: file updated about PivotTable

  9. #9
    Registered User
    Join Date
    05-14-2018
    Location
    Seattle, Washington
    MS-Off Ver
    2016
    Posts
    11

    Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    Okay, that might be a solution. THank you! How did you achieve this example code?

    The pivot table doesn't seem to quite work for what I'm striving for.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Help extracting unique combinations from two columns with criteria that ignores blanks

    Why not a pivot table? there is exactly the same result?

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    Client Season Department Index Value Client Season Department Index Sum of Value
    2
    Bremerton 2018/2019 Software
    1
    264.01
    Bremerton 2018/2019 Hardware
    5
    1180.8
    3
    Bremerton 2018/2019 Hardware
    5
    1180.8
    Bremerton 2018/2019 Software
    1
    264.01
    4
    Total
    1444.8
    Grand Total
    1444.81


    how PowerQuery in this case works you can see....
    • Data Tab
    • Show Queries
    • dbl click on eg. Bremerton table
    • on the right side you'll see steps
    Attached Files Attached Files
    Last edited by sandy666; 05-22-2018 at 11:00 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding unique combinations of 2 columns
    By Scooternm in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-23-2020, 12:41 PM
  2. Sumifs formula that ignores text and blanks in the criteria column
    By sjs4952 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-30-2018, 02:48 AM
  3. [SOLVED] Extracting all unique combinations of elements across three columns
    By bakeraj256 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-12-2014, 09:49 PM
  4. [SOLVED] Unique Combinations from 4 Columns
    By ayvee0 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-12-2013, 06:44 PM
  5. Finding unique combinations across 3 columns using a function
    By Hdim in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-31-2013, 07:17 AM
  6. Replies: 0
    Last Post: 02-02-2013, 04:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1