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 (14.9 KB, 12 views)

Let me see if I understand this. Basically, you want to transpose the column headings in W through AR (e.g. HCP: Break (1), HCP: Break (2), HCP: Breakfast (1), etc.) but not the data under those headings (e.g. 0, 0, 19.98, etc.) and then repeat the data in A through V and AS through AT for each of these.

My computer crashed when I was posting this earlier and I thought my desired file had saved correctly. This attachment clarifies the desired outcome. It is exactly as you described it, but WITH the data transposed under a separate column. Thank you very much for clarifying

Attached Files

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

If you need a macro, please post in the VBA forum

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post