getting a consolidated report

[Chandra Deep Yadav]

Hello forum,

I have a table with multiple items on first column, different regions on first row, and corresponding quantity below, looks something like this,

I want a report as per attached template,


I am trying with inserting a pivot table in each colums via VBA, can anyone please suggest me the simpler way.

Thanks in advance,
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Velen]

Hi Chandra,

Seems like you missed your attachment...

Please do so and i'll try to help...

 

[Chandra Deep Yadav]

M sorry, I forgot to attach the template, Please find the template,

 

[Velen]

Hi Chandra,

There could be 2 possible solutions:

1. Use a Sumif formula included in attached solution. It will check for each column (region) and do the sum for each criteria (part no.)

2. Using a pivot table, but for this you will have the change the way your data is stored (refer to sheet Data Rearranged) in attachment. I have kept the zeroes for your understanding, but if these are removed and you refresh the pivot, this should provide you with your desired results...

If you are using VBA - Option 1 should be much simpler.

Hope this helps!

Cheers,
Velen

 

[Chandra Deep Yadav]

Thanks for the help, can we do that with the click of a button. Also, the regions on the table are not permanent, and these will be changing on case to case case basis. So we will have to list out all the regions every time we will generate the reports. Can we have all these things possible with macros.

Again, lots of thanks

 

[Velen]

Thanks for the help, can we do that with the click of a button. Also, the regions on the table are not permanent, and these will be changing on case to case case basis. So we will have to list out all the regions every time we will generate the reports. Can we have all these things possible with macros.

Again, lots of thanks

Some questions:
1. Does this mean you will keeping this data over different periods (say month, day, weeks...) - Will you need to have a report by period then?
2. You want to have the pivot table at the click of a button or is it the sum if?

 

[Chandra Deep Yadav]

The report will be generated for different products in different regions, and that particular report can be generated at any time.

I actually want the exact final table you prepared on Data Rearranged sheet with a click of a button.

Please let me know if I am not clear.

 

[Velen]

Hi Chandra,

I understand what you are looking form and tt can done with VBA with a click of a button.

I will try to look into it as soon as I have some time.

Cheers,
Velen

 

[jindon]

VBA

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    With Sheets("input")
        a = Sheets("input").Cells(1).CurrentRegion _
        .Resize(.Range("a" & Rows.Count).End(xlUp).Row).Value
    End With
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    b(1, 1) = "Region": b(1, 2) = "Part No": b(1, 3) = "Quantity": n = 1
    For i = 2 To UBound(a, 1)
        For ii = 2 To UBound(a, 2)
            If (a(i, ii) <> "") * (a(i, ii) <> 0) Then
                n = n + 1: b(n, 1) = a(1, ii)
                b(n, 2) = a(i, 1): b(n, 3) = a(i, ii)
            End If
        Next
    Next
    With Sheets("desired output").Cells(1).Resize(n, 3)
        With .CurrentRegion
            .ClearContents
            .Borders.LineStyle = xlNone
        End With
        .Value = b
        .Borders.Weight = 2
        .Parent.Select
    End With
End Sub

 

[Chandra Deep Yadav]

Thanks Jindon, it's working well,

Thanks Velen for your help too!!!

 

[Chandra Deep Yadav]

VBA

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    With Sheets("input")
        a = Sheets("input").Cells(1).CurrentRegion _
        .Resize(.Range("a" & Rows.Count).End(xlUp).Row).Value
    End With
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
    b(1, 1) = "Region": b(1, 2) = "Part No": b(1, 3) = "Quantity": n = 1
    For i = 2 To UBound(a, 1)
        For ii = 2 To UBound(a, 2)
            If (a(i, ii) <> "") * (a(i, ii) <> 0) Then
                n = n + 1: b(n, 1) = a(1, ii)
                b(n, 2) = a(i, 1): b(n, 3) = a(i, ii)
            End If
        Next
    Next
    With Sheets("desired output").Cells(1).Resize(n, 3)
        With .CurrentRegion
            .ClearContents
            .Borders.LineStyle = xlNone
        End With
        .Value = b
        .Borders.Weight = 2
        .Parent.Select
    End With
End Sub

Hi Jindon,

I need to have a column inserted on the same sheet named "Area" in which file shall be reflected in each row till all the part IDs. You can find the desired output sheet attached for more clarified view.