SOLVED

Filtering Columns of Data (Named Ranges) based on Brand Names

Copper Contributor

Hi Everyone,

I am trying to filter columns of data (named ranges) that are on same Excel spreadsheet based on brand names... I want the results of what have been filtered on a different spreadsheet.

Peter

16 Replies

@PeteSauces 

The defined names in the sample workbook are invalid except for one:

HansVogelaar_0-1713303895285.png

@Hans Vogelaar 

 

Sorry!! I forgot to delete the old Named Ranges and make new ones... Please see the new file..

 

Peter

@PeteSauces 

Thanks, the names are valid now. Can you explain what and how you want to filter?

@Hans Vogelaar 

 

I would like all information in the Excel file to remain in the same cells that they are on the first spreadsheet, but at the same time, I want the same information categorized by brandnames on a second spreadsheet (one column for each category)...

 

Does anyone know the formula(s) for this?

 

Peter

@PeteSauces 

Can you provide a few examples of the desired output?

@Hans Vogelaar

 

Attached in this document are the examples for what I want based on the document that I attached previously...

 

But please keep in mind, this is only a gist of the real project, the real data that I have to deal with has a lot more columns...

 

Peter

@PeteSauces 

How can I determine that Scotch-Brite is a brand name, and not 3M?

@Hans Vogelaar 

 

  • Let’s please assume that all products are being filtered using a single search criteria which will be listed in the heading of each column

 

  • Please keep in mind that this is not the actual project, in other words, the actual project has much more information and is completely different than this… but in order to remain anonymous I looked up the names of different products and listed them in the files…

@PeteSauces 

In A2 on the second sheet:

 

=LET(f, VSTACK(FILTER(productLists, ISNUMBER(SEARCH(A1, productLists)), ""), FILTER(Clearance_Products, ISNUMBER(SEARCH(A1, Clearance_Products)), "")), FILTER(f, f<>"", ""))

 

Fill to the right.

@Hans Vogelaar 

 

Can I please have a different column for each filter/search criteria (brand)?

 

Peter

@PeteSauces 

Sorry, you've lost me again. What exactly do you want?

IMG_2796.png

@Hans Vogelaar 

 

This picture shows what the spreadsheet should look like…

 

Peter

best response confirmed by PeteSauces (Copper Contributor)
Solution

@PeteSauces 

See Sheet2 in the attached version. If it is not what you want, please explain.

Yes... This works, thank you!!

@Hans Vogelaar 

 

Another quick question to another problem:

I don't want the filtered results to include results that include words/brands in which, "the word is in a part of another word"....

 

For example, if the filter criteria is "Protect" and one of the products is "Speaker with 10-Year Protection Plan", is there any way to filter using only the word "Protect"...

 

Peter

@PeteSauces 

Use

 

=LET(f, VSTACK(FILTER(productLists, ISNUMBER(SEARCH(" "&A1&" ", " "&productLists&" ")), ""), FILTER(Clearance_Products, ISNUMBER(SEARCH(" "&A1&" ", " "&Clearance_Products&" ")), "")), FILTER(f, f<>"", ""))

 

See the attached version.

1 best response

Accepted Solutions
best response confirmed by PeteSauces (Copper Contributor)
Solution

@PeteSauces 

See Sheet2 in the attached version. If it is not what you want, please explain.

View solution in original post