Take data from one sheet transpose it to another and match the code

[Warren Keogh]

Hi

I have been trying to figure this one out,

I have a list of data. For each ref code there are four possible types (A,B,C,D)

I need to shows for ref code , the number of units for A, then undereath copy the ref code again and show the number of units for for that same code and so forth.

Then beside the number of units I need to show the product number for that type. I need to automate this for large number of transactions.

Not sure where to start even by using helper columns.

Any suggestions very welcome.

(sample data and outcome attached)

 

[jindon]

VBA, if you like

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    a = Sheets("data").[i1].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(a, 1): .Item(a(i, 1)) = a(i, 2): Next
        a = Sheets("data").[a1].CurrentRegion.Value
        For ii = 3 To UBound(a, 2): a(1, ii) = .Item(a(1, ii)): Next
    End With
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    b(1, 1) = a(1, 1): b(1, 2) = "Units": b(1, 3) = "Product No.": n = 1
    For i = 2 To UBound(a, 1)
        For ii = 3 To UBound(a, 2)
            n = n + 1
            b(n, 1) = a(i, 1): b(n, 2) = a(i, ii): b(n, 3) = a(1, ii)
    Next ii, i
    With Sheets("Outcome").Cells(1).Resize(n, 3)
        .CurrentRegion.ClearContents
        .Value = b: .Columns.AutoFit: .Parent.Select
    End With
End Sub

 

[Warren Keogh]

\Hi Jindon

Thanks for your suggestion I was hoping to avoid VBA as I am not very good at VBA especially if something changes.