copy cell

Brass Contributor

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 

 

 

beikme_0-1613570654255.png

 

12 Replies

@beikme 

 

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

 

Thank You
its perfect for what i need

@mathetes 

 

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 

@beikme 

 

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.

@mathetes 

 

=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)

@beikme 

 

??

@mathetes 

Currently i'm filtering betvine multiple rows  

i would like to do it in just one row whit multiple criteria from E1,G1,I1

@beikme 

 

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.

@mathetes 

 

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   

@mathetes 

 

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,"")))

@beikme 

 

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.

@mathetes 

 

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