Find a column list and list down header week with qty

[Spirax]

Need report on Column A which are cust ID that list down week no x qty in 2 columns only
Header Row 1 are week no, (Row can be dynamic too)
Value are ETA qty, (if can select qty or "tbc", empty cell are ignored too)

Thank to advised the vba method

Initial
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD="align: center"]Cust ID / Week
[/TD]
[TD="align: center"]w01
[/TD]
[TD="align: center"]w02
[/TD]
[TD="align: center"]w03
[/TD]
[/TR]
[TR]
[TD="align: center"]xx1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]xx2
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]






After
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Cust ID
[/TD]
[TD]ETA
[/TD]
[/TR]
[TR]
[TD]xx1
[/TD]
[TD]w02x4,w03x2,
[/TD]
[/TR]
[TR]
[TD]xx2
[/TD]
[TD]w01x2,w02x5,
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this for results starting "F1"

[COLOR="Navy"]Sub[/COLOR] MG16Apr20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, nR [COLOR="Navy"]As[/COLOR] Range, 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 To 2)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Application.CountA(Dn.Offset(, 1).Resize(, 3)) > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] R = Dn.Offset(, 1).Resize(, 3).SpecialCells(xlCellTypeConstants)
            c = c + 1
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] nR [COLOR="Navy"]In[/COLOR] R
                Ray(c, 1) = Dn.Value
                Ray(c, 2) = Ray(c, 2) & Cells(1, nR.Column) & "X" & nR & ","
            [COLOR="Navy"]Next[/COLOR] nR
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("F1").Resize(c, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick