Data sorting

[creed2016]

Hi,
I have a set of data - example below - which I need sorting to show each day there is a delivery in the same postcode all on the same line;
Data set example -
[TABLE="width: 500"]
<tbody>[TR]
[TD]Postcode[/TD]
[TD]Route[/TD]
[TD]Drop[/TD]
[TD]Delivery Day[/TD]
[/TR]
[TR]
[TD]AB12 3ZG[/TD]
[TD][TABLE="width: 438"]
<tbody>[TR]
[TD]N3700[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]AB12 3ZG[/TD]
[TD][TABLE="width: 438"]
<tbody>[TR]
[TD]N3700[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]AB24 5EZ[/TD]
[TD]H1006[/TD]
[TD]1[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]AB24 5EZ[/TD]
[TD]H1006[/TD]
[TD]1[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]AL1 4PL[/TD]
[TD]H1006[/TD]
[TD]90[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]AL1 4PL[/TD]
[TD]H1006[/TD]
[TD]90[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]AL4 0LL[/TD]
[TD]H1006[/TD]
[TD]100[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]AL4 0LL[/TD]
[TD]H1006[/TD]
[TD]100[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]AL4 0LL[/TD]
[TD]H1006[/TD]
[TD]100[/TD]
[TD]Friday[/TD]
[/TR]
</tbody>[/TABLE]



I need the following layout but just 1 row with the post code and all delivery days to the right transposed;
[TABLE="width: 438"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Postcode[/TD]
[TD]Route[/TD]
[TD]Drop[/TD]
[TD]Delivery Day[/TD]
[TD]Delivery Day[/TD]
[TD]Delivery Day[/TD]
[/TR]
[TR]
[TD]AB12 3ZG[/TD]
[TD]
[TABLE="width: 438"]
<tbody>[TR]
[TD]N3700[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]25[/TD]
[TD]Wednesday[/TD]
[TD]Saturday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB24 5EZ[/TD]
[TD]N3700[/TD]
[TD]1[/TD]
[TD]Wednesday[/TD]
[TD]Saturday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL1 4PL[/TD]
[TD]H1006[/TD]
[TD]90[/TD]
[TD]Monday[/TD]
[TD]Wednesday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AL4 0LL[/TD]
[TD]H1006[/TD]
[TD]100[/TD]
[TD]Monday[/TD]
[TD]Wednesday[/TD]
[TD]Friday[/TD]
[/TR]
</tbody>[/TABLE]

I tried pivot tables but the values [FONT=&quot]use one of the aggregation functions SUM, COUNT, MIN, AVERAGE etc., so it is always a number.[/FONT]
Help please
Thanks

 

[MickG]

Try this for Results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG26Oct50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count + 1, 1 To 4)
Ray(1, 1) = "Postcode": Ray(1, 2) = "Route": Ray(1, 3) = "Drop": Ray(1, 4) = "Delivery Day"
n = 1
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Dn.Value & Dn.Offset(, 1).Value & Dn.Offset(, 2).Value
    [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
        [COLOR="Navy"]For[/COLOR] ac = 1 To 4
            Ray(n, ac) = Dn.Offset(, ac - 1)
        [COLOR="Navy"]Next[/COLOR] ac
        .Add Txt, Array(n, 4)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Txt)
            Q(1) = Q(1) + 1
            [COLOR="Navy"]If[/COLOR] Q(1) > UBound(Ray, 2) [COLOR="Navy"]Then[/COLOR] ReDim Preserve Ray(1 To Rng.Count + 1, 1 To Q(1))
            Ray(Q(0), Q(1)) = Dn.Offset(, 3)
            Ray(1, Q(1)) = "Delivery Day"
        .Item(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c + 1, UBound(Ray, 2))
    .Value = Ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[creed2016]

Thanks for this Mike but unfortunately it does not seem to have work as I have put the coding into the workbook and it only produced a list like the one I already have.


[TABLE="width: 514"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Postcode[/TD]
[TD]Route[/TD]
[TD]Drop[/TD]
[TD]Delivery Day[/TD]
[TD]Delivery Day[/TD]
[TD]Delivery Day[/TD]
[TD]Delivery Day[/TD]
[/TR]
[TR]
[TD]AB12 3ZG[/TD]
[TD]N3700[/TD]
[TD="align: right"]25[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AB12 3ZG[/TD]
[TD]N6700[/TD]
[TD="align: right"]25[/TD]
[TD]Saturday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AB24 5EZ[/TD]
[TD]N3700[/TD]
[TD="align: right"]1[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AB24 5EZ[/TD]
[TD]N6700[/TD]
[TD="align: right"]1[/TD]
[TD]Saturday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL1 4PL[/TD]
[TD]H1006[/TD]
[TD="align: right"]90[/TD]
[TD]Monday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL1 4PL[/TD]
[TD]H3006[/TD]
[TD="align: right"]80[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]H1006[/TD]
[TD="align: right"]100[/TD]
[TD]Monday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]H3006[/TD]
[TD="align: right"]90[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]H3006[/TD]
[TD="align: right"]91[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]H5003[/TD]
[TD="align: right"]80[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]H5003[/TD]
[TD="align: right"]81[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]N3023[/TD]
[TD="align: right"]90[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]N3023[/TD]
[TD="align: right"]91[/TD]
[TD]Wednesday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]N5019[/TD]
[TD="align: right"]91[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL4 OJJ[/TD]
[TD]N5019[/TD]
[TD="align: right"]90[/TD]
[TD]Friday[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]