Moving Data From Rows to Columns

williamscheidt

New Member
Joined
Dec 7, 2013
Messages
31
Hi,
I'm an Excel beginner running Excel for Mac 2011. I'm in need of assistance in combining data from pairs of subsequent rows into one row, as in the simplified example below. In reality I have 303 pairs of rows (606 total rows) with 14 columns in each.
Thank you in advance for your time and expertise!
Bill

FROM:
123
456
789
101112

<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

TO:
123456
789101112

<!--StartFragment--> <colgroup><col width="65" span="6" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
try like this

drag across and down

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A8</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$C$4,(<font color="Red">QUOTIENT(<font color="Green">COLUMNS(<font color="Purple">$A8:A8</font>)-1,3</font>)</font>)+(<font color="Red">ROWS(<font color="Green">A$8:A8</font>)-1</font>)*2+1,MOD(<font color="Red">COLUMNS(<font color="Green">$A8:A8</font>)-1,3</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />


Excel 2013
ABCDEF
1123
2456
3789
4101112
5
6
7
8123456
9789101112
Sheet1
 
Upvote 0
Hi VBA Geek,

Thanks for the quick response! The formula worked when I applied it to my simple example, but I ran into some snags when applying it to my real data.

Here's what I tried:
=INDEX($A$5:$N$610,(QUOTIENT(COLUMNS($A612:A612)-1,14))+(ROWS(A$612:A612)-1)*2+1,MOD(COLUMNS($A612:A612)-1,3)+1)

Note that I was trying this starting in cell A612 instead of cell A8 in the example. And my data starts in cell A5 and goes to cell N610. Also, the data include text values as well as numerical values (I know that's an important detail that I forgot to mention in my original post.)

Any thoughts?
Thanks!
Bill
 
Upvote 0
williamscheidt,

Here is a macro solution for you to consider.

You can change the raw data worksheet name in the macro.

Sample raw data:


Excel 2007
ABCDEFGHIJKLMN
11234567891011121314
21516171819202122232425262728
32930313233343536373839404142
44344454647484950515253545556
5
Sheet1


After the macro in a new worksheet Results:


Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
112345678910111213141516171819202122232425262728
229303132333435363738394041424344454647484950515253545556
3
Results


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub ReorgData()
' hiker95, 06/08/2015, ME860046
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, nlr As Long, c As Long, nc As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")   '<-- you can change the sheet name here
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  nlr = Application.Ceiling(lr, 2)
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To nlr, 1 To lc * 2)
End With
For i = 1 To lr Step 2
  j = j + 1
  For c = 1 To lc
    o(j, c) = a(i, c)
  Next c
  nc = lc
  For c = 1 To lc
    nc = nc + 1
    o(j, nc) = a(i + 1, c)
  Next c
Next i
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
End Sub

Before you use he macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.
 
Upvote 0
williamscheidt,

running Excel for Mac 2011.

Sorry, I missed that part.

I have no experience with a Mac, but, I would think that it should work because I am using two arrays in memory.

Make sure that you try the macro on a copy of your workbook.
 
Upvote 0
Hi hiker95,

Thanks for the suggestion. Looking at the example you illustrated, what you're suggesting sounds perfect!

I'm a brand new beginner with Macros, so this could be operator error on my part, but when I ran it I got a message stating: run time error 9, subscript out of range. I clicked on Debug and below is what it showed me, except that the text that appears yellow here was actually highlighted in yellow on my screen with a yellow arrow to it's left. The green text below was also actually green on my screen

Any thoughts?

Cheers,
Bill


Sub ReorgData()
' hiker95, 06/08/2015, ME860046
Dim w1 As Worksheet, wr As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr As Long, lc As Long, nlr As Long, c As Long, nc As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Trades List") '<-- you can change the sheet name here
With w1
lr = .Cells(Rows.Count, 1).End(xlUp).Row
lc = .Cells(1, Columns.Count).End(xlToLeft).Column
nlr = Application.Ceiling(lr, 2)
a = .Range(.Cells(1, 1), .Cells(lr, lc))
ReDim o(1 To nlr, 1 To lc * 2)
End With
For i = 1 To lr Step 2
j = j + 1
For c = 1 To lc
o(j, c) = a(i, c)
Next c
nc = lc
For c = 1 To lc
nc = nc + 1
o(j, nc) = a(i + 1, c)
Next c
Next i
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wr = Sheets("Results")
With wr
.UsedRange.Clear
.Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
.Columns(1).Resize(, UBound(o, 2)).AutoFit
.Activate
End With
End Sub
 
Upvote 0
williamscheidt,

Thanks for the update.

Here is an updated macro for you to consider (using two arrays in memory), based on the actual raw data structure, and, results area, you described in your reply #3.

You can change the raw data worksheet name in the macro.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub ReorgDataV2()
' hiker95, 06/08/2015, ME860046
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim nlr As Long, c As Long, nc As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  a = .Range("A5:N610")
  nlr = (610 - 4) / 2
  ReDim o(1 To nlr, 1 To 28)
  For i = 1 To UBound(a, 1) Step 2
    j = j + 1
    For c = 1 To 14
      o(j, c) = a(i, c)
    Next c
    nc = 14
    For c = 1 To 14
      nc = nc + 1
      o(j, nc) = a(i + 1, c)
    Next c
  Next i
  .Range("A612").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgDataV2 macro.
 
Upvote 0
Hiker 95! Thank you!! It worked!! Man, I could buy you a beer!! I ran it on two different worksheets and it did exactly what I was looking for!!

And if I may please ask you just one more question . . . My worksheets will have varying raw data areas, sometimes with more raw data and sometimes with less. I already ran it on a worksheet with less raw data and the macro worked great as is, with the results area beginning at line 612 (I just scrolled down and found them there no problem).

So . . . I'm assuming if I have more raw data than 610 lines, I just adjust the numbers in the macro where appropriate? For example lets say the raw data goes from A5 to N800 and I want the results to start at A802. In this case I'm guessing I would replace all the "610" with "800" and "612" with "802"?

If my line of thinking here is correct, is there a more efficient way to do it than manually adjusting the numbers in the macro? Perhaps just set the range much larger than what I estimate my largest raw data area would be and leave it at that?

Thanks again for your brilliant help!
Bill
 
Upvote 0
And while I'm talking your ear off . . . do you have a macro that can help separate date/time data from one column to two? Currently column C reads: 6/8/15 23:05. I'm looking to have column C read: 6/8/15 while column D would read: 23:05

(I assume I would have to insert a column to the right of C before I ran the macro so it didn't write over the data that's currently in D?)

Thank you!
Bill
 
Upvote 0
williamscheidt,

Hiker 95! Thank you!! It worked!! Man, I could buy you a beer!! I ran it on two different worksheets and it did exactly what I was looking for!!

Thanks for the feedback.

You are very welcome. Glad I could help.

So . . . I'm assuming if I have more raw data than 610 lines, I just adjust the numbers in the macro where appropriate? For example lets say the raw data goes from A5 to N800 and I want the results to start at A802. In this case I'm guessing I would replace all the "610" with "800" and "612" with "802"?

If my line of thinking here is correct, is there a more efficient way to do it than manually adjusting the numbers in the macro? Perhaps just set the range much larger than what I estimate my largest raw data area would be and leave it at that?

1. Will the raw data always begin in A5, and, the last used column is always column N?

2. Are there no blank cells in column A, from A5, down to the last used row in column A?

3. And, the results will be written beginning in column A, two rows below the last used row in column A?

If the above is correct, then, yes, a new macro can be written to accommodate the above.


It is late, and, I will look for your answers tomorrow in the AM.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top