I have an Excel sheet (InData) which has individual rows of data by unique "ID NUMBER". Each ID Number may have multiple "deductions" and "benefits" contained in the one row but which span across many columns. I need to convert the single row of data into multiple rows by ID Number and write the results into a new sheet (OutData).
The Excel data source (InData) may have 10K or more individual rows by unique ID Number. And each ID Number has data going across columns, starting from column “D” and ending in column “BC”. The data has categories (column headings) for deductions or benefits as follows:
Starting in column “D” - Deduction Desc, Deduction Amount, Deduction Start Date, Deduction Stop Date. These four deduction headings are repeated nine times across the columns and end in column “AM”.
Starting in column “AN” - Benefit Desc, Benefit Amount, Benefit Start Date, Benefit Stop Date. These four benefit headings are repeated four times across the columns and end in column “BC”.
Attached is my sample Excel file which contains VBA I'm currently using but needs modification. It has sheets for “InData”, “OutData” (generated by current VBA and not the desired output) and “OutData Needs To Be Like This” (which is desired output).
Thank you!