Microsoft Excel – Transpose stacked data in a column in rows based on variable criteria and unique identifiers


When using Excel 2013, I can hardly 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 in the first row / unique identifier of each stacked record. I think there are at least two possible options to solve this problem, but I have not cracked yet.

  1. Are you writing a formula (or a VBA script? I've only dealt with formulas so far) based on the unique identifiers in the AD columns to sort the deduction code alphabetically or sort by color, and then stack the line at to consistently organize the deduction code column Amount from the Amount column and convert the amounts to the correct deduction code GK columns in the same row as the unique identifiers. (I played with adding an A to the Deduction Code column for the line item's main line, but could not get it to work.)

  2. Formula (or VBA) for automatically sorting the data into groups of 4, 5, or 6 rows, based on the number of rows making up each stacked line item / set of unique identifiers, and alphabeticizing the deduction codes alphabetically. Then apply index or offset formulas or use the transpose function to insert the data into the correct hyphenation G-K columns in the same row as the unique identifiers.

I'm eager to learn new ways Excel can make my life easier, but I'm still working on doing complex computing. These are the only two options that I thought of and tried to figure out, but in the end I could not solve my problem.

I do not care if the problem of converting vertically stacked data based on deduction codes has to be solved in several steps, in the correct columns and in the same line as the unique identifiers (see lines 2, 8, 12 and 17 ).

Any help or solution is welcome, so I do not have to manually group and alphabetize the 4, 5, or 6 lines and transfer the data to the correct columns.

Many Thanks!!

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