#1
|
|||
|
|||
Macro for Column to Rows Data
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. |
#2
|
|||
|
|||
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. |
#3
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Thank you so much!! This is exactly what I needed.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to send several rows of data from one column to each recipient in one go | mikustykus | Mail Merge | 14 | 09-30-2013 08:32 AM |
a function that deletes all rows with no value in a certain column? | dunndealpr | Excel | 2 | 06-08-2013 06:50 AM |
entering data in separate rows within a cell | drsuis | Excel | 1 | 02-19-2013 04:36 PM |
Macro to conditionally delete rows | Steve_D | Excel | 2 | 08-24-2012 09:37 PM |
Concatenated data in subsequent rows | doorsgirl | Excel | 4 | 09-15-2011 10:37 PM |