Forum Discussion

Rvan44's avatar
Rvan44
Copper Contributor
Apr 15, 2022

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 fou...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 17, 2022

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:

company1466140operat 9operat 24operat 20


In order to increase the legibility, do you want to return this instead?

company1466140 operat 24 



 

Rvan44's avatar
Rvan44
Copper Contributor
Apr 18, 2022
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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 18, 2022

    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. 

Resources