Moving Data From Rows to Columns

[williamscheidt]

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:

1	2	3
4	5	6
7	8	9
10	11	12

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

TO:

1	2	3	4	5	6
7	8	9	10	11	12

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

 

[williamscheidt]

Thank you! To your questions:
1) Yes, the raw data will always begin in A5 and the last column is always column N
2) In column A, beginning with A5, every other row has a numerical value beginning with 1 and counting up. So A5 has the value of 1. A6 is blank. A7 has the value of 2. A8 is blank. A9 has the value of 3 etc etc
3) In a perfect world the results would be written on a new sheet in the workbook, ideally titled "Post Macro" or something like that. But, I can always copy and paste, so starting two rows below the last used row in column A will be fine

 

[hiker95]

Thank you! To your questions:
1) Yes, the raw data will always begin in A5 and the last column is always column N
2) In column A, beginning with A5, every other row has a numerical value beginning with 1 and counting up. So A5 has the value of 1. A6 is blank. A7 has the value of 2. A8 is blank. A9 has the value of 3 etc etc
3) In a perfect world the results would be written on a new sheet in the workbook, ideally titled "Post Macro" or something like that. But, I can always copy and paste, so starting two rows below the last used row in column A will be fine

williamscheidt,

Thanks for the new information.

Here is a macro solution for you to consider, based on the above quote, that uses 2 arrays in memory, and, writes the results to a new worksheet Post Macro.

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



Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5	1	2	3	4	5	6	7	8	9	10	11	12	13	14
6		16	17	18	19	20	21	22	23	24	25	26	27	28
7	29	30	31	32	33	34	35	36	37	38	39	40	41	42
8		44	45	46	47	48	49	50	51	52	53	54	55	56
9	57	58	59	60	61	62	63	64	65	66	67	68	69	70
10		72	73	74	75	76	77	78	79	80	81	82	83	84
11
Sheet1

After the macro in a new worksheet Post Macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14		16	17	18	19	20	21	22	23	24	25	26	27	28
2	29	30	31	32	33	34	35	36	37	38	39	40	41	42		44	45	46	47	48	49	50	51	52	53	54	55	56
3	57	58	59	60	61	62	63	64	65	66	67	68	69	70		72	73	74	75	76	77	78	79	80	81	82	83	84
4
Post 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).

Sub ReorgDataV3()
' hiker95, 06/09/2015, ME860046
Dim w1 As Worksheet, wp As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim lr 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.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  a = .Range("A5:N" & lr)
  nlr = (lr - 4) / 2
  ReDim o(1 To nlr, 1 To 28)
End With
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
If Not Evaluate("ISREF(Post Macro!A1)") Then Worksheets.Add(After:=w1).Name = "Post Macro"
Set wp = Sheets("Post Macro")
With wp
  .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 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 ReorgDataV3 macro.

 

[williamscheidt]

Hi Hiker95

Thank you very much! Absolutely perfect! (And sorry for the slow response, I've been on an airplane all day.) I really appreciate your time and expertise with this solution!!

Cheers and have a great evening!
Bill

 

[hiker95]

williamscheidt,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[williamscheidt]

Thanks for the invite hiker95! I've got a lot of work ahead of me in the next coming months so there's a good chance you may see me again. Already I've found a number of answers just by searching the Q's other have already asked. BTW, I answered my own question regarding dates and times in that fashion. I'm loving this forum!