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.

 

[alansidman]

Please use XL2BB and provide a small sample of what your data looks like. Then provide a mocked up sample of what you want the solution to look like based upon your sample data.

 

[MrPink1986]

Please use XL2BB and provide a small sample of what your data looks like. Then provide a mocked up sample of what you want the solution to look like based upon your sample data.

I seemed to be able to sort it out

MrExcel.xlsm
	D
7
Sheet1

This is a sample of the data I am working with

 

[MrPink1986]

MrExcel.xlsm
	A
1	test1
Sheet1

This is my expected out put from the above starting point

 

[MrPink1986]

test1

 

[MrPink1986]

I completed this as a once off for half of the data which took 2 hours - the process was to go into each column when column a is populated and cut and paste the date upwards in the column.

 

[alansidman]

No data provided. If you still need help, suggest you try again and make sure that you have sample data provided.

 

[MrPink1986]

Hi,
First time using the add in so apologies. This is my end state.

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

 

[MrPink1986]

No data provided. If you still need help, suggest you try again and make sure that you have sample data provided.

Apologies on that - I have now attached my starting point - this is my data with the gaps.

MrExcel.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	test1	test2	test3	test4	test5	test6	test7	test8	test9	test10	test11
2	Data	Data	Data	Data	Data	Data	Data	Data	Data		Data
3
4
5
6
7
8
9
10
11							Data	Data	Data		Data
12							Data		Data		Data
13							Data		Data
14							Data		Data
15							Data		Data
16							Data		Data
17
18
19
20							Data		Data
21							Data		Data
22							Data		Data
23							Data		Data
24							Data		Data
25							Data		Data
26							Data		Data
27							Data		Data
28							Data		Data
29							Data		Data
30							Data		Data
31									Data
32									Data
33
34
35
36
37									Data
38									Data
39									Data
40									Data
41									Data
42									Data
43									Data
44									Data
45									Data
46									Data
47
48
49
50
51									Data
52									Data
53									Data
54									Data
55									Data
56									Data
57									Data
58									Data
59									Data
60									Data
61									Data
62	Data Set#2	Data	Data			Data	Data	Data		Data	Data
63							Data	Data			Data
64
65
66							Data	Data
67							Data	Data
68							Data
69
70							Data
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
Sheet1

 

[alansidman]

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