Move large amount of data

[RichAK]

Hi

My data (gage ID’s in this case) are in columns, with three rows in between each one.

There are 16,320 gage ID’s. I need to break them down into groups of 11 and move them to the right of each successive group of 11 ID’s with one column in between each.

If my calculations are correct, I would have to repeat the process over 1400 times to move all the data!

How can this be done quicker?

 

[kweaver]

It would help greatly if you would change your profile to indicate what version of Excel you are running and
use XL2BB to show a few examples of your data (not an image) and the expected results.

 

[RichAK]

Excel for Office 365 -

From:

Book2
	A	B
1	id	Count
2	GP .0040Z-11	1
3
4
5
6	GP .0040Z-12	2
7
8
9
10	GP .0040Z-18	3
11
12
13
14	GP .0040Z-21	4
15
16
17
18	GP .0040Z-22	5
19
20
21	GP .0040Z-9	6
22
23
24
25	GP .0042A	7
26
27
28
29	GP .0042B	8
30
31
32
33	GP .0042C	9
34
35
36
37	GP .0043A	10
38
39
40
41	GP .0043B	11
42
43
44
45	GP .0044A	12
46
47
48
49	GP .0044B	13
50
51
52
53	GP .0044C	14
54
55
56
57	GP .0045C	15
58
59
60
61	GP .0045I	16
62
63
64
65	GP .0045J	17
66
67
68
69	GP .0045L	18
70
71
72
73	GP .0045M	19
74
75
76
77	GP .0045N	20
78
79
80
81	GP .0048A	21
82
83
84
85	GP .0049A	22
86
Sheet1

To:

Book2
	A	B	C	D
1	id	Count
2	GP .0040Z-11	1	GP .0044A	12
3
4
5
6	GP .0040Z-12	2	GP .0044B	13
7
8
9
10	GP .0040Z-18	3	GP .0044C	14
11
12
13
14	GP .0040Z-21	4	GP .0045C	15
15
16
17
18	GP .0040Z-22	5	GP .0045I	16
19
20
21
22	GP .0040Z-9	6	GP .0045J	17
23
24
25
26	GP .0042A	7	GP .0045L	18
27
28
29
30	GP .0042B	8	GP .0045M	19
31
32
33
34	GP .0042C	9	GP .0045N	20
35
36
37
38	GP .0043A	10	GP .0048A	21
39
40
41
42	GP .0043B	11	GP .0049A	22
Sheet1

 

[offthelip]

I think I understand what you are trying to do, try this code. I have assumed all the data is on the active sheet. just for test I have selected "sheet2" to output the results, so be aware that this will overwrite sheet2, change this as necessary when it works correctly :

Sub test()
Dim outarr As Variant

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
inarr = Range(Cells(1, 1), Cells(lastrow, lastcol))
'Range(Cells(1, 1), Cells(lastrow, lastcol)) ' change this line from a comment when you want to write back to the active sheet ( this clears it!!)
' define output array we need 2 columns for each 44 rows and we need 2 columns for each output
orow44 = Round((1 + lastrow / 44), 0)
ocol = lastcol * orow44
ReDim outarr(1 To 44, 1 To ocol)
rowno = 2
colno = 1
For j = 1 To lastcol Step 2
 For i = 2 To lastrow Step 4
  outarr(rowno, colno) = inarr(i, j)
  rowno = rowno + 4
  If rowno > 44 Then
   rowno = 2
   colno = colno + 2
  End If
 Next i
 rowno = 2
 colno = colno + 2
Next j
Worksheets("sheet2").Select
Range(Cells(1, 1), Cells(44, ocol)) = outarr

End Sub

Note it will be fast because it uses variant arrays and only accesses the worksheet twice

 

[RichAK]

Offthelip,
That was amazing! Thank you so much!!!!

 

[Peter_SSs]

Excel for Office 365 -

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know. (Don’t forget to scroll down & ‘Save’). Then it will show up on every post like this