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

Cust ID / Week	w01	w02	w03
xx1		4	2
xx2	2	5

<tbody>
</tbody>

After

Cust ID	ETA
xx1	w02x4,w03x2,
xx2	w01x2,w02x5,

<tbody>
</tbody>

 

[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