Feb 17 2021 06:06 AM
Hi all
Have small issue
I need to copy information to different cell if the number from A column is the same as in other spreadsheet and bring all the cells in row B to another row
I tried VLOOKUP but its keep bringing just the first value
Feb 17 2021 07:03 AM
The new FILTER function should work, if I understand what you're asking. That is available in the newest versions of Excel. Here's a reference site that can help you with the syntax for your own situation. If you need further help, please attach a copy of your actual spreadsheet rather than just an image.
https://exceljet.net/excel-functions/excel-filter-function
Feb 23 2021 02:26 AM
Hi
Just quick question in example its just red and blue
What if i have more criteria then 2 lets just say I have red , blue , black and white
how would i get to show all black and white
hope you understood the question
Feb 23 2021 04:36 AM
Just quick question in example its just red and blue
What example are you referring to? The one that started this chain? Or a purely hypothetical/imaginary one?
What if i have more criteria then 2 lets just say I have red , blue , black and white
how would i get to show all black and white
I presume you mean only black and white; stated another way, what you mean is "I have four conditions, red, blue, black and white" and you want to select rows meeting two criteria, satisfying either or two conditions. You do that, using filter, by using something like the following for the criteria section within the FILTER function. (CellRange="black")*(CellRange="white)
hope you understood the question
I hope so too. If not, please come back and clarify. Even better, attach a working sample spreadsheet. It's always easier to work with something closely resembling what one is asking about.
Feb 23 2021 06:03 AM
=FILTER('[Master Label Maker - White Goods 2.0.xlsx]Data White Goods'!$A:$B,'[Master Label Maker - White Goods 2.0.xlsx]Data White Goods'!$A:$A=E1)
Feb 23 2021 10:09 AM
Currently i'm filtering betvine multiple rows
i would like to do it in just one row whit multiple criteria from E1,G1,I1
Feb 23 2021 11:24 AM
If I understand what you're saying, the formula would be this:
=FILTER(Sheet1!A:B,(Sheet1!A:A=Sheet2!E1)+(Sheet1!A:A=Sheet2!G1)+(Sheet1!A:A=Sheet2!I1))
The + sign between criteria effectively means match A:A with E1 OR G1 OR I1.
If you want to get sexy, here's the same formula using the new LET function.
=LET(
Srce,Sheet1!A:B,
MtSrce,Sheet1!A:A,
FILTER(Srce,(MtSrce=Sheet2!E1)+(MtSrce=Sheet2!G1)+(MtSrce=Sheet2!I1))
)
Here's what LET does; in effect it's saying
LET "Srce" stand in for Sheet1!A:B in the final formula
LET "MtSrce" stand in for Shhet1!A:A in the final formula
now do the final formula using those variables
And you could make the named variables there even shorter.
Feb 23 2021 11:18 PM
Thank You
This works amazing
just one more question, i'm dealing whit a lot of data,is there any whey to get rid of 0 and make it not look for them empty spaces if G1 is empty
Feb 23 2021 11:50 PM
I was trying this but it don't work
=FILTER(Sheet1!A:B,(IF(E1>1,Sheet1!A:A=Sheet2!E1,""))+(IF(F1>1,Sheet1!A:A=Sheet2!F1,"")))
Feb 24 2021 04:51 AM
Interesting. If you put anything into G1--try "-" for example, or even simply a space--and the formula still works, without returning 0. G1 then isn't empty, but it's a criterion that nothing meets. So FILTER shows nothing for that criterion.
I'm sure there's some kind of way to use IF, or some other conditional but that would get convoluted.
Not knowing what your real world application is, my "workaround" solution would be to make sure G1 always has something in it, even if it's nonsense.
Feb 24 2021 07:43 AM
Yes I agreed the simple whey is just to put something in there but i work whit dum people and they will always find a whey to brake something