Filtering records on criteria AFTER filtering out blank rows

[WildBurrow]

I'm circling the drain here.
I have a table with 53 columns and 302 rows. Column A is populated with sequential serial numbers, while Columns B through BC are blank.

Once a record is created, Columns B through BC are populated with specific information.

I need to create a filter that will;
1) filter out blank rows (determined by Column B cell being empty), then
2) filter those records based upon a value in a specific cell (BQ1) that matches table values in Column BC, then
3) filter those records based upon an array value (e.g. "Assessment", "Response", "Stabilization", etc.), then finally
4) display those results and show only eight specific columns (column numbers 1,2,4,7,14,15,17,41)

What I've tried so far:

1). If I write the formula to only include records that I know have values in Column B, I can get it to return a list which is filtered by Column BC and the specific array explained in item 3 above, and finally filtered to show the eight columns noted in item 4 above. This is the closest I have gotten to getting the desired results.​

​

=FILTER(FILTER(A2:BC116,(BC2:BC116=BQ1)*(ISNUMBER(MATCH(B2:B116,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))),{1,1,0,1,0,0,1,0,0,0,0,0,0,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0})​

​

I've tried to include (B2:B302:<>"") in various locations of the above formula, but get one of the infamous error messages, such as #Calc! and #Value! (please don't ask which one, I've tried so many ways, that I can't recall which position triggered which error).​

​

2). Conversely, I can get it to filter blank rows(based upon empty cells in Column B) and show only the eight columns noted in item 4 above.​

​

=FILTER(FILTER(A2:BC302,(B2:B302<>""),"No Records"),{1,1,0,1,0,0,1,0,0,0,0,0,0,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0})​

​

I've tried to include (BC2:BC116=BQ1) in various locations but it would also return with error messages.​

​

My mind tells me that I need to remove the blank rows first, then filter the data based upon the values in Column BC matching the cell value in BQ1, then finally filtering out unwanted columns. I just can't seem to figure out the language and/or order.

Any suggestions?

 

[Fluff]

If col B is blank then those rows should be filtered out as they don't match the criteria in the MATCH function, so this should work

=LET(f,FILTER(A2:BC302,(BC2:BC302=BQ1)*(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))),INDEX(f,SEQUENCE(ROWS(f)),{1,2,4,7,14,15,17,41}))

 

[WildBurrow]

If col B is blank then those rows should be filtered out as they don't match the criteria in the MATCH function, so this should work

=LET(f,FILTER(A2:BC302,(BC2:BC302=BQ1)*(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))),INDEX(f,SEQUENCE(ROWS(f)),{1,2,4,7,14,15,17,41}))

Fluff,

Thanks for the reply. When I placed your formula on my sheet, I received a #NAME? error.

I've looked at my ranges (which you referenced in your formula) and they seem correct. I looked for typos and found none.

If I understand your formula (which is limited as I've just watch a bunch of YouTube videos );

1. You used the LET to assign the name "f" to the filter function
2. The filter function is using the array (A2:BC302) and including BC2:BC302 if it matches the value in BQ1
3. The filter function is then using the AND expression to look for an exact match for a list of words in B2:B302
4. Then "f" rows and columns are indexed and sequenced

What am I missing?

Vikki

 

[Fluff]

If you are getting #NAME error that suggests that your xl is not fully updated & so you don't have the LET function.
In which case you can just use your original formula & change the ranges.

 

[WildBurrow]

If you are getting #NAME error that suggests that your xl is not fully updated & so you don't have the LET function.
In which case you can just use your original formula & change the ranges.

Fluff,

Now things are getting tricky I'm not sure exactly which parts of my original formula I need to change.

From your first reply "If col B is blank then those rows should be filtered out as they don't match the criteria in the MATCH function".

If I write my original formula array to exclude known empty rows: =(FILTER(A2:BC116,(BC2:BC116=BQ1)*(ISNUMBER(MATCH(B2:B116,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))))

• I get a listing of records

If I write my original formula array to include known empty rows: =(FILTER(A2:BC302,(BC2:BC302=BQ1)*(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))))

• I get a "#N/A" error

It appears as though my original formula will not filter out the blank rows based upon Column B values matching my list of values. Not sure why.

Secondly, my original formula filtered the filter results to show my desired columns. Your formula suggestion did an index and sequence of rows which were defined by "f". If I try to analyze my color brackets when typing the formula, I get a bit confused as to what is included in "f"; and therefore, I am unable to replace it with my ranges.

Is "f": (A2:BC302,(BC2:BC302=BQ1), or
if "f": (A2:BC302,(BC2:BC302=BQ1)*(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0))))

Again, any help will be greatly appreciated.

 

[Fluff]

If you are getting a #N/A error, that suggest that the error exists in the actual sheet.

 

[WildBurrow]

If you are getting a #N/A error, that suggest that the error exists in the actual sheet.

Fluff,

Well, I've discovered the error on my sheet. It was a vlookup error that was returning the #N/A in column BC (one of my filter options). I've added the "if(iserror " to that column and have managed to get the data presented as I desire.

My final formula is this:

=IF(ISERROR(FILTER(FILTER(A2:BC302,(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))*(BC2:BC302=BG2)),{1,1,0,1,0,0,1,0,0,0,0,0,0,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0})),"",FILTER(FILTER(A2:BC302,(ISNUMBER(MATCH(B2:B302,{"Assessment","Response","Stabilization","Remediation / Recovery"},0)))*(BC2:BC302=BG2)),{1,1,0,1,0,0,1,0,0,0,0,0,0,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0}))

This has been a very good learning experience for me. Thank you for your help.

 

[Fluff]

Glad you sorted it & thanks for the feedback.