Excel help sorting.

[Rvan44]

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

[#2]

@Rvan44 

=FILTER(A6:I52,(G6:G52=K3)+(H6:H52=K3)+(I6:I52=K3))

Maybe with this formula. Enter the filter criteria dynamically in cell K3.

[#3]

@Rvan44 

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

[#4]

I am running 365. 

@Peter Bartholomew 

[#5]

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. :frowning_face:

[#6]

@Rvan44 

In that case, this should demonstrate the formula works.  It may be a couple of steps too far for implementation but, hopefully, of interest.

[#7]

As you can tell I’m not super advanced with excel. So bare with me. I have the drop down list on H3 ( this is where the accounts are listed.) Do I need to put the formula too left corner cell where I want the result displayed?

[#8]

@Rvan44 

You are correct.  That relationship between the formula location and the placement of the results is the key feature that makes spreadsheets an implementation of functional programming, as opposed to imperative programming.   Each value is determined by its definition (associated by sharing a cell) and it cannot change as a result of the state of the calculation elsewhere.

You may well find @OliverScheurich's implementation easier to follow.  He has written out tests for the 3 columns explicitly and used '+' as the equivalent of an 'OR' operator acting row by row.  All I have done is to use more modern functionality 'BYROW' to run through the dataset row by row and embedded the 'OR' within a Lambda function so that it can accept the row ranges from 'BYROW'.

I might even speculate that the more experience one has of traditional spreadsheet working, the harder the move to modern Excel.  Others may be of a different opinion!

[#9]

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.

[#10]

@Rvan44 

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

[#11]

@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?

[#12]

@Rvan44 

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

[#13]

@Rvan44 

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.

[#14]

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.

[#15]

@Rvan44 

=LET(operator1,IF(FILTER('sheet 1'!G6:G52,('sheet 1'!G6:G52=K3)+('sheet 1'!H6:H52=K3)+('sheet 1'!I6:I52=K3))<>K3,"",K3),
operator2,IF(FILTER('sheet 1'!H6:H52,('sheet 1'!G6:G52=K3)+('sheet 1'!H6:H52=K3)+('sheet 1'!I6:I52=K3))<>K3,"",K3),
operator3,IF(FILTER('sheet 1'!I6:I52,('sheet 1'!G6:G52=K3)+('sheet 1'!H6:H52=K3)+('sheet 1'!I6:I52=K3))<>K3,"",K3),
company,FILTER('sheet 1'!A6:F52,('sheet 1'!G6:G52=K3)+('sheet 1'!H6:H52=K3)+('sheet 1'!I6:I52=K3)),
result,CHOOSE({1,2,3,4,5,6,7,8,9},company,company,company,company,company,company,operator1,operator2,operator3),
result)

Maybe with this formula.