Using tags to filter

Good morning to all Excelforum members

I am a new member, and other than my introduction post, this is my first post.

I will set the scene. I am using Excel as a 'Bill of Materials (BOM)'.

I have 5000+ components listed in Column H "COMPONENT DESCRIPTION". Think of the components like Meccano parts, that can be used to make lots of different PRODUCTS.

I would like to be be able to use Column "I" for "PRODUCT TAGS". My aim here is to list the "product tags" in the single cell next to each component. By then filtering the tags it would allow me to sort my component by product.

Some of the components that are used across multiple products may need up to 10 x tags. Others may only be specific to one product, so will only need 1 x tag.

By filtering by product I will reduce the list of component down to 100's rather than 1000's, and in turn make the production of a BOM much more efficient and less prone to error.

This is where I've currently got too.

I'm listing PRODUCT TAGS in Column I like this:
;STARLOCK;;ORBIT;;VERTECH;;SEATING;;ULTRA;;TITAN;

Then filtering by 'text', then 'contains' e.g. *;ORBIT;*.

This feels really clunky and I hope they'll be a much slicker way, ideally by simply ticking the Product Tag I wish to filter by.


Key information.
I'm using Office 365, 64 bit.
We use Sharepoint, so Macros are frowned upon. A macro free solution is desired.
I'd prefer the PRODUCT TAGS in a single column, not across multiple columns.
I'd like to filter by selecting multiple product tags, not just 1 or 2. However if this is a step too far, I can make it work with 1 or 2 selections.

I'm hopeful there is someone out there who can help. Fingers crossed

Hi AliGW

I'll work on that now and come back to you...

Hi again

Sample spreadsheet attached, I hope.

Please refer to my first post for the description of the problem I am wishing to overcome.

Additional questions:

1. Is it possible to filter specific columns only, rather than all columns? For example, I would like Column I- "PRODUCT TAGS" and Column Q - "TOTAL" filtered but none of the others..

2. When filtering either of the above columns, is it possible to keep the rows that are filled in Grey visible e.g. STARLOCK, HANDRAILS, SEATING etc.

Please let me know if you need any further information.

Hi AliGW - thanks for coming back to me.

I haven't yet looked at GROUP option in the Data Ribbon - I will look into that now and see if that helps. I am a very basic user of Excel but I'll give it a whirl.

The previous attached sample was my attempt to highlight the problem I'm describing in my first post. Not sure exactly how else to show/explain what I'm trying to achieve.

Column I is Product Tag column. If you use the filter drop down you will see that I can filter by by 'text', then again by 'contains' e.g. *;ORBIT;*.

This feels really clunky and I hope they'll be a much slicker way, ideally by simply ticking the Product Tag I wish to filter by.

One workaround is to apply conditional formatting in column I with this formula:=IF(I1="",TRUE,SEARCH($I$2,I1)>0), then filter the "PRODUCT TAG" by font color.

Type in the text you want to filter in cell I2, then re-apply the filter > Data > Sort & Filter > Reapply.

A filter...

Formula:

Please Login or Register  to view this content.