Excel – Pivot table – Summarising multiple independent data columns

microsoft excel

I have a large table containing daily inputs into a system, which has the columns as shown:

Day Itemname1 Itemquant1 Itemname2 Itemquant2 …..

The item names and quantities are independent of each other. On one day, one might receive 10 Item A, and on another day, one might receive 5 of Item D, 6 of Item B, and 234 of Item C. In the former case, only 2 colums will have data, and in the latter case, 6 columns will have data. All the other columns will be empty. An example:

Day N1  Q1  N2  Q2  N3  Q3
1   A   10
2   D   5   B   6   C   234

How would a pivot table be used to display, say, the quantities of A daily over a year, or the number of non-zero values of A in a year? Note that A may appear in multiple columns, and there are hundreds of possible items, so simply creating a table with each variable in a column is not feasible.

I have tried adding all of the columns in multiple ways, but it ends up returning the values of A, given all the values of B, given all the values of C, which is useful if the values are dependent, but clutters up the page since they are uncorrelated.

If this is not possible, what would be a good method to obtain the information required from the spreadsheet?

Best Answer

You desperately need to reorganize your data. It's a really bad idea to have an indeterminate number of columns for reasons you're running into now. My advice is, as others have suggested, to convert your data to a simple three-column table with a row for each daily item.

Day  Item  Quant
 1     A     10
 2     D      5
 2     B      6
 2     C    234

Then you can use a pivot table to summarize and drill down into the data as you wish.

So, now the issue is how to go about reorganizing this data in an indeterminate number of columns. Luckily, a fairly simple VBA procedure can do this. Paste the code below into a new VBA module (Press Alt+F11, then go to Insert > Module).

Sub SalvageMyTable()
Dim sOrig As Worksheet, sOut As Worksheet
Dim arrIn() As Variant
Dim rOut As Range, arrOut() As Variant
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set sOrig = ActiveSheet
'Store original data in an array for faster processing
arrIn = sOrig.UsedRange.Value
'Create maximal output array. This can be resized before outputting the data.
ReDim arrOut(0 To 2, 0 To UBound(arrIn, 1) * (UBound(arrIn, 2) - 1) / 2) As Variant
'Loop through original table and copy values to output array.
For i = LBound(arrIn, 1) + 1 To UBound(arrIn, 1)
    For j = LBound(arrIn, 2) + 1 To UBound(arrIn, 2) Step 2
        If arrIn(i, j) <> "" Then
            arrOut(0, k) = arrIn(i, LBound(arrIn, 2))
            arrOut(1, k) = arrIn(i, j)
            arrOut(2, k) = arrIn(i, j + 1)
            k = k + 1
            Exit For
        End If
    Next j
Next i
'Resize output array
ReDim Preserve arrOut(0 To 2, 0 To k) As Variant
'Create output sheet and print output there.
Set sOut = Worksheets.Add
sOut.Name = "Reorganized Data"
sOut.Range("A1").Value = "Day"
sOut.Range("B1").Value = "Item"
sOut.Range("C1").Value = "Quantity"
sOut.Range("A2").Resize(k, 3).Value = Application.WorksheetFunction.Transpose(arrOut)
'Reset Excel settings
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Be sure to have the worksheet with your original data open. Then go to the VBA editor and run this code (by pressing F5). This code will output the transformed data in three columns on a new worksheet named "Reorganized Data".