I have Excel data in an unusable format for upload to a system. I need to replicate each row of data, but also convert select columns of data into rows.

In the attached file, I have two sample rows of data and a row of headers in the Current Data tab.

Columns A thru V need to be repeated in new rows to align with converting Columns W thru AR into a single column, but broken out by rows to align with the newly repeated rows for Columns A thru V.

In the Desired Data tab, you can see the results I would like as described above.

I'm not an expert in developing Macros, but this is something that would be repeated monthly for several thousand rows of data, initially.

Attached Files

Current to Desired Data Outcome.xlsx (15.1 KB, 8 views)

Hi,

Here is a template you can use.
Transfer your source data into the CurrentData sheet and then run the macro.
A sheet called: NewData will get created with the new data construct.

Template attached.

Attached Files

Data Construct Template.xlsm (28.1 KB, 10 views)

You can also get the desired result without macros. See attached. Compared to a macro solution, this has the advantage of automatically adjusting the outputs to reflect any changes in the inputs. To use, essentially all you do is copy the A2:Z23 block (or the A2:Z45 block) on the 'Desired Data' sheet and paste the formulae and/or formats there on the subsequent rows as often as needed.

Note: You could make the replication process simpler, by adding a formula (=OFFSET('Current Data'!$A$1,INT((ROW()-2)/22),MOD(ROW()-2,22)+COLUMN()-1)) to W2 and simply copying all of row 2 down as far as needed, but that would add unnecessary bulk to the saved workbook.

Attached Files

Data Parsing.xlsx (21.0 KB, 12 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you so much!! This is exactly what I needed.