Excel Macro for Splitting one Sheet into Multiple sheets - Based on two Values

Question

Hi, 

I have a table that I need splitting based on value in Col G ( Cat 3,4,5,C,Unknown) I would also like to then split that data based on a letter in Col D. I can run a Micro to split the data in Col G (Corrected Category) but then cant seem to find a micro that will then split the data based on a unique Letter within a number on Col D. 

Here's a sample of the the workbook I want to split. I can run a Micro to split the data from column G in to tabs so that I get all data in each Category (Col G) on separate tabs 

But then I would like to spit Category 3 into two tables based on the unique Letter in Col D (SPN) the letter will always be an "L" or a "H". So now the rows in yellow "H" and the other 3 rows "L" will be on new tabs/sheets.

So basically if its even possible in VBA I want to splits data based on column G and D so I end up with two sheets (tabs) for each category one with the H numbers and one with the L number filtered from Col D. 

thanks

Matty.

Answer 1

• The simplest thing is to use the FILTER function, if your version supports it, on ten different sheets.
• Another macro based solution would be to add a column with a formula like

=G2 & IF(ISNUMBER(FIND("L",UPPER(D2))),"L","H")

and use your splitting macro on that column of formulas.

• An easy alternative to the macro if you don't have the FILTER function is to create 10 power queries, and filter G for each of your 5 possible values, then filter D based on contains H or L - then just refresh the queries when you have a new data set.