Using Google Sheet filter function with multiple conditions
Hello all,
In this sheet:
https://docs.google.com/spreadsheets/d/13hZ8cdOksyHh07FVftuWsWtwuKavrG2RC7134CLiwZ4/edit#gid=977037083
Tab "Align set 1 and 2" compares data found on tabs "target" and "source".
Tab "Final Export (filter function)" is generated based on a "yes" condition in column D (keep target) of the first tab.
Now I want to add a few extra conditions... in tab "NEW Final Export (filter function)" I want to generate content based on several conditions in column D (keep target) of the first tab:
If a "yes" condition - pull data from the "target" tab. - What it does now.
If any other condition (no, "", etc.) pull data from the "source" tab.
My current function line that works is:
=Filter(target!$A1:I,'Align set 1 and 2'!$D2:D<>"yes")
I have tried multiple iterations of multiple conditions without success, but everything I tried results in an error message like, "Filter has mismatched range sizes"
=Filter(target!$A1:I,'Align set 1 and 2'!$D2:D<>"yes", source!$A1:I994,'Align set 1 and 2'!$C2:C1000<>"yes") - this doesn't work. :(
Thank you.
In this sheet:
https://docs.google.com/spreadsheets/d/13hZ8cdOksyHh07FVftuWsWtwuKavrG2RC7134CLiwZ4/edit#gid=977037083
Tab "Align set 1 and 2" compares data found on tabs "target" and "source".
Tab "Final Export (filter function)" is generated based on a "yes" condition in column D (keep target) of the first tab.
Now I want to add a few extra conditions... in tab "NEW Final Export (filter function)" I want to generate content based on several conditions in column D (keep target) of the first tab:
If a "yes" condition - pull data from the "target" tab. - What it does now.
If any other condition (no, "", etc.) pull data from the "source" tab.
My current function line that works is:
=Filter(target!$A1:I,'Align set 1 and 2'!$D2:D<>"yes")
I have tried multiple iterations of multiple conditions without success, but everything I tried results in an error message like, "Filter has mismatched range sizes"
=Filter(target!$A1:I,'Align set 1 and 2'!$D2:D<>"yes", source!$A1:I994,'Align set 1 and 2'!$C2:C1000<>"yes") - this doesn't work. :(
Thank you.
ASKER
Thank you, Professor J.
Your method may actually solve another problem that I was encountering with how I was originally trying to accomplish this task.
But there is a problem with your suggested formula that you could not anticipate. Your method assumed that the "trans unit IDs" would always match. Unfortunately, there will be mismatches. I have edited the "Source" tab content to reflect that reality.
But your method is still a brilliant idea. Thank you.
Your method may actually solve another problem that I was encountering with how I was originally trying to accomplish this task.
But there is a problem with your suggested formula that you could not anticipate. Your method assumed that the "trans unit IDs" would always match. Unfortunately, there will be mismatches. I have edited the "Source" tab content to reflect that reality.
But your method is still a brilliant idea. Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Amazing! Utterly brilliant. Thank you so much.
I was looking at this for so long and you found a much better approach!
I was looking at this for so long and you found a much better approach!
You are most welcome! thanks for the feedback.
ASKER
The only "problem" is that using your method is that I wanted to avoid the need to copy/paste the formula down about 5000 lines; that how much data I will need to compare.:O
But I guess I am dealing with the limitations of a spreadsheet.
Another idea that I had was to create two tabs using the Filter function... one tab would capture the "yes" condition, the next tab would capture <> "yes" condition, and then merge the results of each tab.
But the Filter function comes with a caveat... my intention is to use the sheet as a template where I import new data via .csv into the source and target tabs. The Filter function fails by complaining about mismatched ranges (as above). I'm not sure how to work around that. Therefore, it seems that your method will probably work better.
But I guess I am dealing with the limitations of a spreadsheet.
Another idea that I had was to create two tabs using the Filter function... one tab would capture the "yes" condition, the next tab would capture <> "yes" condition, and then merge the results of each tab.
But the Filter function comes with a caveat... my intention is to use the sheet as a template where I import new data via .csv into the source and target tabs. The Filter function fails by complaining about mismatched ranges (as above). I'm not sure how to work around that. Therefore, it seems that your method will probably work better.
Another idea that I had was to create two tabs using the Filter function... one tab would capture the "yes" condition, the next tab would capture <> "yes" condition, and then merge the results of each tab.
if you want with merging result it is also possible. So, I created a new sheet and placed this formula ={Filter(target!$A1:I900,'Align set 1 and 2'!$D1:D900<>"yes");Filter(source!A1:I900,'Align set 1 and 2'!$D1:D900="yes")}
the reason I used the max row 900, because your google sheets were protected and I could not use the whole Column reference as there were mismatch of number of rows available in each of the source sheets.
ASKER
Oh wow... that's how that is done!
Thank you, thank you. :) This is perfect. :)
Thank you, thank you. :) This is perfect. :)
:)
ASKER
Hello,
I'm back again. I've created a new EE question so that you can get extra points for solving a related issue. :)
https://www.experts-exchange.com/questions/29211770/Understanding-error-messages-when-using-the-Google-Sheets-Filter-Function.html#questionAdd
I'm back again. I've created a new EE question so that you can get extra points for solving a related issue. :)
https://www.experts-exchange.com/questions/29211770/Understanding-error-messages-when-using-the-Google-Sheets-Filter-Function.html#questionAdd
Hi Doug, I will take a look
I have placed the formulas in the NEW Final Export (filter function) sheet
in A1 of this sheet i placed an array ={'Align set 1 and 2'!A2:A}
then in B2 of the same sheet I placed =if('Align set 1 and 2'!$D3="yes",vlookup($A2,target!$A$2:$I,Columns($A$1:B1),0),vlookup($A2,source!$A$2:$I,Columns($A$1:B1),0))
see the result in https://docs.google.com/spreadsheets/d/13hZ8cdOksyHh07FVftuWsWtwuKavrG2RC7134CLiwZ4/edit#gid=1733811331