Filtering columns based on "containing" multiple sets of letters

[Batikh]

Hi All,

I work for a TV manufacturer and I need help filtering out incoming tickets based on model series.

For example, the attachment below is a sample of tickets extracted to excel, I manually highlighted models of the same series to show what I mean.

I'm looking for a formula or a way of sorting where I can show modes of the same series. I'm currently using the contain option for text filters but it's not very efficient as I only get to select 2 sets of letters at a time.

I hope I've made it as clear as possible but please let me know if more info is needed.

I'm kind if Excel newbie but I'm really looking forward to knowing more so any help is appreciated.

Kind regards,
B

 

[Taul]

Hi,
welcome to the board.
I'm not sure what you mean by "Series". Could you explain what you mean by series please, just so I can understand how they are to be separated.
The two yellow cells are 49XH and 65XH, how are they the same series?

cheers
Paul.

 

[Batikh]

Hi Taul,

Thanks for replying!

I'll try to provide as much info as possible without releasing sensitive company info.

A series is a group of models who share similar internal components and it's defined by the 2 letters in the middle and the two following numbers. So in the example given let's say we have 3 different series: A, B and C. So "XH95" and XH80" both belong to series A (Among many others). following the same logic, "XE90" and "XG80" belong to series B and so on.

When extracting the list of tickets from our system it has many different variants of different series based on specs and screen size so ideally, I'm looking for a formula where I can feed in the filtering info i.e. which model codes belong to which series to be able to filter or categorise the column based on this info. (for example "If the cells in that column contain XH90 then it's series A so colour in green).

I hope I've made things clearer and thanks again for helping out.

Cheers,
Batikh

 

[Taul]

Hi,
You could use "Advanced Filter" but it would probably be better to change your arrangement to a layout as shown below OR copy the data to another page.
Also, you can record a macro to replicate the Advanced Filter actions and assign the macro to a button.
The advanced filter uses a criteria range where you can specify what you need to search for, in this case, I used ? as a wild card (KD-??XH9???) so I can search for the XH9000 series

Book1
	A	B	C	D
1	Model	Ticket
2	Television	KD-??XH9???	<--Criterria
3
4	Model	Ticket	<-- Your List
5	Television	KD-49XH9505
6	Television	KD-65XH9077
8	Television	KD-55XH9005
11
12
13
14
15
16
Sheet2

I recorded a macro as shown below, you can adapt it to suit your own sheets or record your own macro.

Sub Macro2()
    Application.CutCopyMode = False
    Range("A4:B10").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:B2"), Unique:=False
End Sub

 

[Taul]

Hi,
Further thoughts.
You can simply use Conditional Format to colour the cells rather than filter them.
In the example below I used the CF formula of :-

=SEARCH("*XH9*",$B2)

Advanced Filter.xlsm
	A	B	C
1	Model	Ticket
2	Television	KD-49XH9505
3	Television	KD-65XH9077
4	Television	KD-65XH8077
5	Television	KD-55XH9005
6	Television	KD-75XE9005
7	Television	KD-65XG8096
8
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=SEARCH("*XH9*",$B2)	text	NO

You would need to generate different CF formula and colour for each condition you need to search for.

The alternative would be to create a list of which ticket numbers belong to series A, B & C and then filter or CF on that list