VBA Macro to compare a column with a list of values

[gtabib]

Hi everyone. I am new with VBA macro and need help to address a work issue.
I have a column in my data set and need to compare the values in this column (HHS) with a master list and insert a blank row every time the data is missing in column HHS.
I know there are already other posts regarding this, but I couldn't find the exact match for my requirement.
Below is my original data set along with the master list (lookup list) and expected result.

Original data

Month	HHS	Month order	Emergency Claimed $
July	Cairns and Hinterland	1.00	0
July	Central Queensland	1.00	12
July	Darling Downs	1.00	41
July	Gold Coast	1.00	11
July	Metro North	1.00	111
July	Metro South	1.00	14
July	Sunshine Coast	1.00	41
July	Townsville	1.00	21
July	West Moreton	1.00	47
July	Wide Bay	1.00	23
August	Cairns and Hinterland	2.00	52
August	Central Queensland	2.00	56
August	Darling Downs	2.00	45
August	Gold Coast	2.00	41
August	Metro North	2.00	12
August	Metro South	2.00	45
August	Sunshine Coast	2.00	45
August	Townsville	2.00	0
August	West Moreton	2.00	12

Master List that column B (HHS) needs to be compared with:
Carins and Hinterland
Central Queensland
Central West
Darling Downs
Gold Coast
Mackay
Metro North
Metro South
North West
South West
Sunshine Coast
Torres and Cape
Townsville
West Moreton
Wide Bay

Expected Result (a loop to return to the first row after each month and check the next month)

Month	HHS	Month Order	Emergency Claimed $
July	Carins and Hinterland	1	0
July	Central Queensland	1	12
July	Darling Down	1	41
July	Gold Coast	1	11
July	Metro North	1	111
July	Metro South	1	14
July	Sunshine Coast	1	41
July	Townsville	1	21
July	West Moreton	1	47
July	Wide Bay	1	23

Appreciate any help!

 

[AlanSidman]

A power query solution

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"HHS"}, T2, {"Column1"}, "T3", JoinKind.RightOuter),
    #"Expanded T3" = Table.ExpandTableColumn(MQ, "T3", {"Column1"}, {"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded T3", each ([Month] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Month", Order.Descending}, {"HHS", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column1"})

in
    #"Removed Columns"

 

[p45cal]

Likewise a Power Query solution in the attached. See table at cell H1.
Needs refreshing like a pivot table (right-click and choose Refresh)
I've left HHS showing so you can see what's missing for what month.

 

[vletm]

gtabib​

Here a sample VBA solution.
It shows also those HHSs, which don't match with Your data.
Select Result-sheet to see results.

 

[gtabib]

gtabib​

Here a sample VBA solution.
It shows also those HHSs, which don't match with Your data.
Select Result-sheet to see results.

Thanks so much for your help. It's awesome, can you please review your code because it ignores the first HHS which is Cairns and Hinterland. In my original data this HHS exist (in first table) however when running your code the result tab does not show this HHS. The code is perfectly working though! Thanks again!

 

[gtabib]

gtabib​

Here a sample VBA solution.
It shows also those HHSs, which don't match with Your data.
Select Result-sheet to see results.

Likewise a Power Query solution in the attached. See table at cell H1.
Needs refreshing like a pivot table (right-click and choose Refresh)
I've left HHS showing so you can see what's missing for what month.

View attachment 86564

Thanks so much!

 

[gtabib]

A power query solution

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"HHS"}, T2, {"Column1"}, "T3", JoinKind.RightOuter),
    #"Expanded T3" = Table.ExpandTableColumn(MQ, "T3", {"Column1"}, {"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded T3", each ([Month] <> null)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Month", Order.Descending}, {"HHS", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Column1"})

in
    #"Removed Columns"

Thanks so much for your help!

 

[vletm]

gtabib​

Your it ignores the first HHS which is Cairns and Hinterland.
... because texts are different Cairns <> Carins .
Data
HHS
Should I guess Your original data?
or compare number of letters?

 

[gtabib]

gtabib​

Your it ignores the first HHS which is Cairns and Hinterland.
... because texts are different Cairns <> Carins .
DataView attachment 86591
HHSView attachment 86590
Should I guess Your original data?
or compare number of letters?

Thanks, my bad! I adjusted the spelling in the HHS tab, and it fixed the issue. Again, thank you so much!