Extracting data using multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2097000%22%20slang%3D%22en-US%22%3EExtracting%20data%20using%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2097000%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20this%20possible%20in%20%22one%20go%22%20to%20extract%20rows%20from%20a%20large%20excel%20data%20set%20using%20multiple%20criteria.%20I%20have%20a%20long%20list%20of%20companies%2C%20and%20each%20is%20-%20among%20other%20things%20-%20identified%20by%20an%20industry%20SIC%20code.%20I%20am%20looking%20to%20extract%20those%20companies%20that%20match%20a%20set%20of%20SIC%20codes%20(eg%2010%20codes)%20in%20one%20go.%20Of%20course%2C%20I%20can%20use%20the%20filters%2C%20but%20that%20would%20involve%20(too)%20much%20manual.%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2097000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2097364%22%20slang%3D%22de-DE%22%3ESubject%3A%20Extracting%20data%20using%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2097364%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942695%22%20target%3D%22_blank%22%3E%40Clauscapitellum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20probably%20not%20the%20right%20one%20to%20suggest%20a%20solution%20in%20this%20direction.%20But%20with%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20Picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is this possible in "one go" to extract rows from a large excel data set using multiple criteria. I have a long list of companies, and each is - among other things - identified by an industry SIC code. I am looking to extract those companies that match a set of SIC codes (eg 10 codes) in one go. Of course, I can use the filters, but that would involve (too) much manual.    

4 Replies

@Clauscapitellum 

I am probably not the right one to suggest a solution in this direction. But with your permission, if I can recommend you, add a file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

 

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@Nikolino Many thanks for your reply ... however, I found a solution using the XLOOKUP function which - when I inserted a "cheat column" of data in my data set that would be displayed when a value match was found (e.g. a "fake phone number" from the official example/here a "1". The "cheat dataset" is useful for subsequent sorting .. so all well

 

  

@Clauscapitellum 

Alternatively

 

=FILTER(range,COUNTIF(SICtoSelectrange,rangeSIC))

 

Many thanks, Sergei. Claus