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

[PeteSauces]

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

[#2]

@PeteSauces 

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

[#3]

@HansVogelaar 

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

Peter

[#4]

@PeteSauces 

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

[#5]

@HansVogelaar 

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

[#6]

@PeteSauces 

Can you provide a few examples of the desired output?

[#7]

@HansVogelaar

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

[#8]

@PeteSauces 

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

[#9]

@HansVogelaar 

• 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…

[#10]

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

[#11]

@HansVogelaar 

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

Peter

[#12]

@PeteSauces 

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

[#13]

 

@HansVogelaar 

This picture shows what the spreadsheet should look like…

Peter

[#14 · Accepted solution]

@PeteSauces 

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

[#15]

Yes... This works, thank you!!

[#16]

@HansVogelaar 

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

[#17]

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