Transpose Stacked Data in One Column to Rows based on Variable Criteria and Unique Identifiers

Question

Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/unique identifiers of each stacked data set. I'm thinking there are at least two potential options to solving this but I haven't cracked either yet.

1. Write a formula (or VBA script? I've only dabbled in formulas so far) based on the unique identifiers in columns A-D to alphabetize or sort by color the deduction code by stacked line item to consistently organize the Deduction Code column to then pull the amount from the Amount column and transpose the amounts into the correct deduction code G-K columns on the same row as the unique identifiers. (I played with adding an A to the deduction code column for the main line item row but couldn't get that to work either.)

2. Formula (or VBA) to automatically sort the data into groups of 4, 5, or 6 rows, based on how many rows make up each stacked line item/set of unique identifiers, and alphabetize the deductions codes. Then apply Index or Offset formulas or use the transpose function to put the data into the correct deduction code G-K columns on the same row as the unique identifiers.

I enjoy learning new ways Excel can make my life easier but am still working on my execution of complex data processing. These are the only two options I've thought of and tried to figure out but ultimately could not solve my issue.

I don't care if it takes several steps to solve my problem of converting vertically stacked data based on deduction codes into the correct columns and the same row as the unique identifiers as shown on rows 2, 8, 12, and 17.

Any help or solutions are welcome so I don't have to manually group and alphabetize the 4, 5 or 6 rows together then transpose the data into the correct columns.

Thank you!!

Link to the file: https://drive.google.com/file/d/1jNpuRWsFqPRTo9J3-g4X1Qss5nFPRdET/view?usp=sharing

Answer 1

You're right that there are many ways to move data. A formula can do what you want. A pivot table is another way to move values to the same row.

Before you try a pivot table, fill the empty cells in columns B, C, and D. Here's a macro to fill empty cells with the value in the cell above.

Sub FillEmptyCellsWithTheValueAbove()
'Select a range of cells, then run this macro to fill empty cells 
'with the value in the cell above.
    Dim rngCell As Range
    For Each rngCell In Selection.Cells
        If rngCell.Value = "" Then
            rngCell.Value = rngCell.Offset(-1, 0).Value
        End If
    Next rngCell
End Sub

In column E, fill the empty cells with "AA" or any value that precedes "EX" alphabetically.

When you define the pivot table, the rows will be Name, Date, Product Type, and Decimal. The columns will be Deduction Code. The values will be Sum of Amount. See the first image.

On the Design ribbon, adjust the layout of the pivot table. You probably want these options:

• Subtotals > "Do Not Show Subtotals"
• Grand Totals > "Off for Rows and Columns"
• Report Layout > "Show in Tabular Form"
• Report Layout > "Repeat All Item Labels"

For the result of those options, see the second image. To improve the pivot table further, format the columns for dates and numbers.