Need VBA to create cross table from rawdata end with sumifs function

Hi experts

I have set of data which contains sales data Product ,unit price and week

i want generate cross wizard table from rawdata
as column C:C value fill unique value in column A:A Start at A9
as column E:E value fill unique value in Row(8:8) start B8

fill formula across =SUMIFS(Data!$D:$D,Data!$C:$C,$A9,Data!$E:$E,B$8)&B9/$B$18 result as 5 (10%)

find the attachment

hi sir thanks for your time i have huge data process where column week nos extent pivot ask space sum and percentage of sum, if you can help with vba ,more helpful for ever.

Hi,

what could be the objective reason for those useless duplicate columns in your expected result ?‼

its my explanation but incomplete , please find the attachment its my expectation output if i add more data in raw file column and row variable should expend depend upon unique count.

As it's obviously better to use codes rather than names in case of any typo or upper-lower cases issue
but as here the Product Code column was created by Dumb or Dumber
so the names should be used and in case of any name issue you could amend the VBA procedure yourself
if the codes column is smarter in your real workbook …

And using useless merged cells could raises somme issue
but you could amend the VBA procedure yourself in such case …

i cleared merged cells and made genuine view please have a look , help me

According to the post #7 attachment a VBA demonstration for starters :

PHP Code: 

Sub Demo1()
    Dim C%, W, R&, L&, V(), P&
        C = 1
        W = Sheet2.[A1].CurrentRegion.Columns("B:D")
    With New Collection
        On Error Resume Next
    For R = 2 To UBound(W)
       .Add .Count + 2, W(R, 1)
    Next
        On Error GoTo 0
        L = .Count + 2
        ReDim V(1 To L, 1 To C)
        V(1, C) = "Sale Table"
        V(L, C) = "Grand Total"
    For R = 2 To R - 1
        P = .Item(W(R, 1))
        If IsEmpty(V(P, 1)) Then V(P, 1) = W(R, 1)
        If W(R, 3) <> W(R - 1, 3) Then C = C + 1: ReDim Preserve V(1 To L, 1 To C): V(1, C) = W(R, 3)
        V(L, C) = V(L, C) + W(R, 2)
        V(P, C) = V(P, C) + W(R, 2)
    Next
    End With
    For C = 2 To C
    For R = 2 To L - 1
        V(R, C) = V(R, C) & Format(V(R, C) / V(L, C), " (0%)")
    Next R, C
    With Sheet1.[A8].Resize(L, C - 1)
        .CurrentRegion.Clear
        .Borders.Weight = 2
        .HorizontalAlignment = xlCenter
        .NumberFormat = "  @  "
         Union(.Rows(1), .Rows(L)).Interior.ColorIndex = 24
        .Value = V
        .Columns.AutoFit
         Application.Goto .Parent.[A1], True
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

johnlara,

try

Please Login or Register  to view this content.

Thanks Alot Boss Saved my project greateful to you its solved

last question where do i make change if i want do average function instead of count in result range fill from B9:D17.