Forum Discussion
Excel help sorting.
=FILTER(A6:I52,(G6:G52=K3)+(H6:H52=K3)+(I6:I52=K3))Maybe with this formula. Enter the filter criteria dynamically in cell K3.
OliverScheurich
Happy Easter! So I kept messing with it after I posted the reply. I ended up deleting the file and made a new copy of my master sheet. So I knew it would be 100% clean. Meaning no left behind functions I tried before I asked help on this forum.and you are correct it works flawlessly.
Thank you so much for your patience and help. ( and that goes for everyone who responded ) Now have another question or maybe 2.
Can I put this on sheet 2 and let it grab the information from sheet 1?and to make it more legible can I return a blank cell for any value that does not match my search criteria?
To move stuff from sheet 1 to Sheet 2, grab the region you wish to move with a mouse select and use Ctrl/X for 'Cut'. Turn to sheet 2 and type Ctrl/V in the corresponding location. Assuming you are using FILTER, the function contains another parameter which allows you to return a message or, even, to perform an alternative function.
Happy Easter to you too, thank you!
=FILTER('sheet 1'!A6:I52,('sheet 1'!G6:G52=K3)+('sheet 1'!H6:H52=K3)+('sheet 1'!I6:I52=K3))The above formula grabs the data from sheet 1.
The result includes this data for company 1:
company1 4 6 6 14 0 operat 9 operat 24 operat 20
In order to increase the legibility, do you want to return this instead?company1 4 6 6 14 0 operat 24 - Yes, you are spot on again. That is exactly what I want it to look like. After getting everything set I noticed that it would be nice to only show the value from the search criteria to reassure the end user ( my coworkers that it is returning only the lines that contain that set criteria.
It is definitely it a must but something my OCD would like to see LOL.
Thanks again.
- Good evening,
I tried this from scratch and copied exactly what you have. For some reason excel is giving me an error. It tells me there is a problem with the function if I click okay it highlights the I52. It tells me this (FILTER(array,include,[if empty])
I tried using filter function for just 1 column like so
=FILTER(A6:I52,G6:G52=K3,)
And it pulls it up just fine have no idea what I’m doing wrong especially if this works for you.
Thank for all your help.=FILTER(A6:I52,COUNTIF(OFFSET(G6:I6,ROW(1:47)-1,0),K3)>0)You can try the above formula to filter multiple occurences of the value entered in cell K3.
=FILTER(A6:I52,(G6:G52=K3)+(H6:H52=K3)+(I6:I52=K3))I don't understand why this formula doesn't work in your sheet. Can you attach a screenshot of your file where the data, formula and error is shown?
- That is exactly how I tried doing it. I made a list somewhere else on the sheet with all the account names on it and created a filter box just like you did. So I can click on the box it will show a drop down and I can choose the account I want. The one thing I can’t get done is for it to display every row with set account name. ☹️
