VBA To copy specific rows many time depending on Europena Postcode data in each row and filter data from cells.

[jamesworger]

Hi,

Background: I have a list of shipping prices that depend on; Country; Postcode; Weight. Many postcodes have the same price but are not in number sequence, for example postcodes 5000-7999 will be the same cost as postcodes 11000-12999. Our website CMS system, Magento, requires all shipping costs to be in postcode order, thus I need to upload a list showing the price for 5000-7999 cost, then 8000-10999 cost, then 11000-12999 costs.

I have added below two sample worksheets:

• Master - this is the list I currently have with the correct prices, Col.B shows all postcode bands that particular price covers (each separated by a colon)
• List - this is what I want the VBA formula to produce, a list of all prices, but with only 1 postcode band per line.

Notes:

• Postcodes are seen as text on our website, thus we need to put 1-4999 and 0001-499 as a postcode, in case a customer puts a postcode with or without the 0's before.
• Ideally, 1 button would be pressed and, from the Master worksheet, the List worksheet is created.

Is this possible and if so, how would you suggest I go about this and can you offer any advice?

Master worksheet:
[TABLE="width: 850"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Postcode[/TD]
[TD]Weight[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
</tbody>[/TABLE]


List Worksheet:
[TABLE="width: 539"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Postcode[/TD]
[TD="align: right"]Weight[/TD]
[TD="align: right"]Cost[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
</tbody>[/TABLE]

 

[jamesworger]

MickG,

And if the Postcode field is blank, it is counted as unique and copies those rows to the new table?


Thanks

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG05Mar45
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Q           [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Ray()
[COLOR="Navy"]Dim[/COLOR] oTxt        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    oTxt = Dn.value & Dn.Offset(, 2).value
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(oTxt) [COLOR="Navy"]Then[/COLOR]
        Dic.Add oTxt, Array(Dn.Offset(, 2), Dn.Offset(, 3))
    [COLOR="Navy"]Else[/COLOR]
        Q = Dic.Item(oTxt)
        [COLOR="Navy"]Set[/COLOR] Q(1) = Union(Q(1), Dn.Offset(, 3))
        Dic.Item(oTxt) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] P [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]




[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] Dic.keys
    Sp = Split(Dic.Item(K)(0), ";")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
            
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] P [COLOR="Navy"]In[/COLOR] Dic.Item(K)(1)
                c = c + 1
                ReDim Preserve Ray(1 To 5, 1 To c)
                Ray(1, c) = Dic.Item(K)(0).Offset(, -2)
                Ray(2, c) = P.Offset(, -2)
                Ray(3, c) = "'[COLOR="Green"][B]" & Sp(n)[/B][/COLOR]
                Ray(4, c) = P
                Ray(5, c) = P.Offset(, 1)
            [COLOR="Navy"]Next[/COLOR] P
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]With[/COLOR] Range("G1")
    .Resize(, 5).value = Array("Country", "Region", "Postcode", "Weight", "Cost")
    .Offset(1).Resize(c, 5) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jamesworger]

MickG,

Can a blank Postcode field be counted as unique and appear in the new table?

 

[MickG]

If you do this, do you not get what you want????
[TABLE="width: 486"]
<tbody>[TR]
[TD]1-4999;00001-04999; ;50001-55999;60001-61999[/TD]
[/TR]
[TR]
[TD]1-4999;00001-04999; ;50001-55999;60001-61999[/TD]
[/TR]
[TR]
[TD]1-4999;00001-04999; ;50001-55999;60001-61999[/TD]
[/TR]
[TR]
[TD]1-4999;00001-04999; ;50001-55999;60001-61999[/TD]
[/TR]
</tbody>[/TABLE]

 

[jamesworger]

I do get the right result, but if the entire Postcode field cell is "", so no value at all, can it be seen as a unique and copied to the new table?

You example showed a blank part of a cell with values in. I mean if the entire cell is blank.

 

[MickG]

Please show an example with the Blank cells !!