Forum Discussion
Excel help sorting.
Good afternoon, hope someone can help.
so here goes.
i have a spreadsheet that has 8 columns the first Column is a list of companies named and numbered starting in A6 down to A52. Then I have four columns with general info and the last three columns named operator 1, operator 2, operator 3. These are the columns where you will have to input an operator name from a list of +- 35 operator names and these can be put in either column G, H and I. With that said you can have the same name in any of the columns but on a different row.
hope the above makes sense.
now here is what I want to do. I want to be able to filter all 3 columns at the same time. I created a drop down list with all 35 operators as I thought I could then validate that against columns G, H and I. And simply display that on second sheet.
thanks
14 Replies
Do you have Excel 365, because FILTER is one of the dynamic array functions that came out in 2018.
The following involves lambda functions simply because I am trying to change the way in which I program Excel.
= FILTER(table, BYROW(operators=selected,ORλ)) where ORλ = LAMBDA(x,OR(x))In that case, this should demonstrate the formula works. It may be a couple of steps too far for implementation but, hopefully, of interest.
=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.
- 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. ☹️
