Generate Multiple Rows Of Data From One Row of Data That Spans Across Many Columns

  • 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!

  • Re: Generate Multiple Rows Of Data From One Row of Data That Spans Across Many Column


    Here is replacement code for the ReOrgNew macro. You can play around with the HorizontalAlignment section at the end to get the data to show up how you want it.


  • Re: Generate Multiple Rows Of Data From One Row of Data That Spans Across Many Column


    shknbk2, your replacement code is fantastic. It does exactly what is required. Thank you very much!