Filtering columns based on "containing" multiple sets of letters

Batikh

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
  2. Web
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.
1608636991693.png


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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
ABCD
1ModelTicket
2TelevisionKD-??XH9???<--Criterria
3
4ModelTicket<-- Your List
5TelevisionKD-49XH9505
6TelevisionKD-65XH9077
8TelevisionKD-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.

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

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
752
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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 :-
Code:
=SEARCH("*XH9*",$B2)

Advanced Filter.xlsm
ABC
1ModelTicket
2TelevisionKD-49XH9505
3TelevisionKD-65XH9077
4TelevisionKD-65XH8077
5TelevisionKD-55XH9005
6TelevisionKD-75XE9005
7TelevisionKD-65XG8096
8
Sheet3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B7Expression=SEARCH("*XH9*",$B2)textNO


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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,781
Messages
5,626,837
Members
416,204
Latest member
Perzo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top