Transposing Numbers

[howard]

I have and would like to transpose all if these one below the other. Have shown example of what it must look like after transposing the data

I have a huge amount of data to transpose

Excel 2012
	A	B	C	D	E
1	33004	33005	33007	33008	33009
2	33003	33006
3	33244	33245	33247	33248	33249
4	33243	33246
5	33024	33025	33027	33028	33029
6
7	33004
8	33005
9	33007
10	33008
11	33009
12	33006
13	33245
14	33247
15	33248
16	33249
17	33246
18	33025
19	33027
20	33028
21	33029
Sheet1

 

[howard]

Thanks for your help Mark. Your code works perfectly and is super fast

 

[GTO]

Hi Howard,

You are most welcome of course, but most of that was Rick's, just a couple of differences.

Happy coding

Mark

 

[hiker95]

howard,

My screenshots show that the macro worked correctly.

Do my screenshots show what your raw looks like, and, what the results should look like?

If not, can we see what your raw data actually looks like?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[howard]

Thanks for your reply and input

This is how it looks before being transposed

Excel 2012
	A	B	C	D	E	F	G
1	4700N	173016	173018
2	4700U	173066	173068
3	4701	173022	173072	173222	173272
4	4704N	173020
5	4704U	173070	173220	173270
6	4720	173266	173268
7	4721	173216	173218
8	4730N	173612
9	4730U	173662	173712
10	4731N	173614
11	4731U	173664	173714
12	4733	173616	173666	173716	173766	173816
13	4736	173316	173318	173320	173322	173812	173814
14	4737	173912
15	4746	174070	177023
16	4750	173762
17	4751	173764
18
19
Sheet1

This is how it looks after being transposed

Excel 2012
	A
1	173016
2	173018
3	173066
4	173068
5	173022
6	173072
7	173222
8	173272
9	173020
10	173070
11	173220
12	173270
13	173266
14	173268
15	173216
16	173218
17	173612
18	173662
19	173712
20	173614
21	173664
22	173714
23	173616
24	173666
25	173716
26	173766
27	173816
28	173316
29	173318
30	173320
31	173322
32	173812
33	173814
34	173912
35	174070
36	177023
37	173762
38	173764
Sheet2

 

[hiker95]

howard,

Thanks for the screenshots.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G
1	4700N	173016	173018
2	4700U	173066	173068
3	4701	173022	173072	173222	173272
4	4704N	173020
5	4704U	173070	173220	173270
6	4720	173266	173268
7	4721	173216	173218
8	4730N	173612
9	4730U	173662	173712
10	4731N	173614
11	4731U	173664	173714
12	4733	173616	173666	173716	173766	173816
13	4736	173316	173318	173320	173322	173812	173814
14	4737	173912
15	4746	174070	177023
16	4750	173762
17	4751	173764
18
Sheet1

After the macro (using two arrays in memory) in worksheet Sheet2:

Excel 2007
	A
1	173016
2	173018
3	173066
4	173068
5	173022
6	173072
7	173222
8	173272
9	173020
10	173070
11	173220
12	173270
13	173266
14	173268
15	173216
16	173218
17	173612
18	173662
19	173712
20	173614
21	173664
22	173714
23	173616
24	173666
25	173716
26	173766
27	173816
28	173316
29	173318
30	173320
31	173322
32	173812
33	173814
34	173912
35	174070
36	177023
37	173762
38	173764
39
Sheet2

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 ReorgData_V2()
' hiker95, 09/07/2014, ME803687
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long, c As Long
Dim lr As Long, lc As Long, n As Long
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w1
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = Application.Count(.Range(.Cells(1, 2), .Cells(lr, lc)))
  ReDim o(1 To n, 1 To 1)
End With
For i = 1 To lr
  For c = 2 To lc
    If a(i, c) <> "" Then
      j = j + 1
      o(j, 1) = a(i, c)
    End If
  Next c
Next i
With w2
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(n, 1).Value = o
  .Columns(1).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

Then run the ReorgData_V2 macro.

 

[howard]

Thanks for the help, much appreciated. At first Macro did not work, but once I converted the data to numbers on Sheet1, code then ran perfectly

 

[hiker95]

howard,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.