Macro to move date in columns to ensure there are no blank rows

[MrPink1986]

Hi there,

I have a excel sheet which is almost 11k lines and 11 columns big.
Within the columns data is populated sporadically in the rows and I want to be able to bring this data into a defined structure.
The defined structure is that Row 2 will be completed across all columns where there is data in the columns and so on until the next piece of data appears in column a.
Column A will only have one entry per data set.
So A2 has data and A45 has data - between B2 and K44 I want all the data to aligned in the column with no gaps
Row 1 is header data.

Any ideas of this can be done with a macro?
I am leaning towards doing this via filters and uploads - if I record this it wont work as there are lots of variables in the columns with the data.

Any help is muchly appreciated.

 

[MrPink1986]

I can see your raw data, but I am not sure what your mocked up solution should look like. Am I missing something here?

So the difference essentially is that in the raw data there a gaps in the rows between A2-K61 - essentially if there is data in column B4 only I need this to be in B2. All data in the columns between the data in column A needs to have no blank rows as represented in the second dump.

In cell G11:G:16 this data should be moved up to G3 and then the data G20:G30 underneath that.
Each column will have a variable of data that needs to be cut and pasted to the previous blank row in the column

 

[alansidman]

I am sorry to be a pest, but I see no difference in the two spreadsheets that you have presented. They appear to have the same data presented in the same arranged format. You will need to provide a better explanation and example as what you are saying makes no sense to me given the examples presented.

 

[MrPink1986]

MrExcel.xlsm
	A			D			G	H	I	J	K
1	test1			test4			test7	test8	test9	test10	test11
2	Data			Data			Data	Data			Data
3							Data	Data
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23							Data
24							Data
25							Data
26							Data
27							Data				Data
28							Data				Data
29							Data
30							Data
31							Data
32							Data
33							Data
34							Data
35							Data
36							Data
37							Data
38							Data		Data
39									Data
40									Data
41									Data
42									Data
43	Data Set#2						Data	Data		Data	Data
44							Data	Data			Data
45							Data
46
47
48
49
50
51											Data
52
53							Data
54							Data
55							Data
56								Data
57								Data
58
59
60
61
62
63
64
Sheet1

No problem I appreciate your patience with me and hopefully after this we are clear - in this example I have my data - there are essential two data sets here. A2:K42. In these data - each column has data and there are some gaps within it. I want all the data in the columns to sit in to the next free row above and afterwards there should be no gaps between row 2 and the data in the columns. So if we look at column I there is data in I38:I42 - I want this data cut and pasted above in to I2, the next available blank row. This should be repeated for all columns.
The next data set is from A43:K57 - the data set is identified from the data in column A - from A2 to the next value is the data set and likewise in column A. IN my actual data there is hundreds of data sets in column A, here I only have included 2 examples.

 

[MrPink1986]

Here is the data and what the expected result looks like with the data presented in the way I outlined above.

MrExcel.xlsm
	A			D			G	H	I	J	K
1	test1			test4			test7	test8	test9	test10	test11
2	Data			Data			Data	Data	Data		Data
3							Data	Data	Data		Data
4							Data		Data		Data
5							Data		Data
6							Data		Data
7							Data
8							Data
9							Data
10							Data
11							Data
12							Data
13							Data
14							Data
15							Data
16							Data
17							Data
18							Data
19							Data
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43	Data Set#2						Data	Data		Data	Data
44							Data	Data			Data
45							Data	Data			Data
46							Data	Data
47							Data
48							Data
49
50
51
52
53
54
55
56
57
58
59
60
After