Mar 25 2020 09:51 AM
Hi all,
I believe this is a simple one.
Sheet1
2 columns: column A contains text cells, column B contains info on the category of the text in A.
I need to transfer/copy into Sheet2 all the cells from column A which belong to two categories only.
Can it be done automatically, with either formulas (better) or macros?
Thank you.
Mar 25 2020 10:16 AM
The FILTER function can do it. I've been able to make it work with your data on one category at a time; I've not yet been able to incorporate an OR that would enable it to do both categories---but I'll keep trying.
Here's the formula: =FILTER(Foglio1!A2:B35,Foglio1!B2:B35="WHITE")
Mar 25 2020 10:25 AM
Well, there may be a more efficient way, but with a "Helper" column to identify the WHITE or RED categories in your first table, this FILTER function worked. See the attached file
=FILTER(Foglio1!A2:B35,Foglio1!C2:C35="Y")
Mar 25 2020 10:45 AM
Thank you very much @mathetes .
It appears that the FILTER function is not available on my Excel version (Home 2019)!
Mar 25 2020 11:24 AM
Hmm...I'm on Excel at home; here's the product info ...
So then, you might just use the ability that's more longstanding, on the Data tab, to Filter. Put your cursor in one of the cells in your table and select Filter (you'll see it there on the right in the screen capture below). Once that has appeared, you can select the criteria you want by clicking on the little caret in the column heading and selecting the red and white categories, as I've done in the second image
after which you can copy and paste.
Mar 25 2020 12:29 PM
Dear @mathetes ,
Actually the FILTER function available with Office 365 is the right one.
The manual filtering option requires the user to take an action, that is what I need to avoid.
Any other suggestion, maybe with macros, which could hit the target?
Thank you
Mar 25 2020 01:30 PM
I'd assume that a macro of some kind could do this. I am not a user of macros, however, so I'll defer to someone else on that.
Mar 25 2020 02:04 PM
Thank you very much @mathetes for the support.
Much appreciated.