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

1. ## 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.

3. ## 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. ## 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. ## Re: Help extracting unique combinations from two columns with criteria that ignores blanks

maybe this one will be ok

6. ## 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. ## 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. ## 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

9. ## 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. ## 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

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