If this does not do what you want (test in a copy of your workbook) you might have to paint a better picture or give a small screen shot of the top part of say 10 columns "before" and "after". My signature block has suggestions for doing that in a way that we can copy your data to test.
1. Select rows 1 to the bottom of any heading data by click & drag down the row numbers at the left of the sheet.
2. F5 - Special.. - Blanks - OK
3. Right click one of the highlighted blank cells - Delete... - Shift cells up - OK
B | C | D | E | F | G | H | I | J | |
---|---|---|---|---|---|---|---|---|---|
44 | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories |
45 | 1926 | 1926 | 1926 | 1926 | 1926 | 1926 | 1926 | 1926 | 1926 |
46 | 1726 | 1726 | 1726 | 1726 | 1726 | 1726 | 1726 | 1726 | 1726 |
47 | 3935 | 3935 | 3935 | 3935 | 3935 | 1834 | 1834 | 1834 | 1834 |
48 | 3934 | 3934 | 3934 | 3934 | 3934 | 1815 | 3934 | 3934 | 1815 |
49 | 1914R | 1914R | 1914R | 3619 | 1914R | 2417 | 1914R | 1914R | 2417 |
50 | 1815 | 1815 | 1815 | 1815 | 1815 | 2434 | 3614 | 3614 | 2418 |
51 | 6811 | 6885 | 6885 | 5814 | 5814 | 2417X | 1815 | 3619 | 2514 |
52 | 6817 | 6887 | 6887 | 5814A | 5814A | 2514 | 3714 | 1815 | 2413R |
53 | 6831 | 5814 | 5814 | 5814R | 5814R | 9950 | 3717 | 3714 | 9950 |
54 | 6818 | 5814A | 5814A | 5814X | 5814X | 3931 | 3617 | 3717 | 5185 |
55 | 6820 | 5814R | 5814R | 6811 | 6811 | 5185 | 3617R | 3617 | 4974 |
56 | 6821 | 5814X | 5814X | 6817 | 6817 | 4974 | 3617X | 3617R | 3677A |
57 | 6825 | 6811 | 6811 | 6831 | 6831 | 3677A | 2514 | 3617X | 3677 |
58 | 6825X | 6817 | 6817 | 6818 | 6818 | 3677 | 3716 | 2514 | 3677X |
59 | 6826 | 6831 | 6831 | 6820 | 6820 | 3677X | 9950 | 3616 | 3678 |
60 | 6811X | 6818 | 6818 | 6821 | 6821 | 3678 | 5185 | 9950 | 3678X |
61 | 2514 | 6820 | 6820 | 6825 | 6825 | 3678X | 4974 | 3931 | 3678A |
62 | 5813 | 6821 | 6821 | 6825X | 6825X | 3678A | 3677A | 5185 | 4976 |
63 | 9950 | 6825 | 6825 | 6826 | 6826 | 4976 | 3677 | 4974 | 5186A |
64 | Chassis & Attachments | 6825X | 6825X | 6811X | 6811X | 5186A | 3677X | 3677A | 5186 |
65 | 5526 | 6826 | 6826 | 6833X | 6833X | 5186 | 3678 | 3677 | 2712 |
66 | 5526R | 6811X | 6811X | 6833 | 6833 | 2712 | 3678X | 3677X | 2615 |
67 | 1914R | 6833X | 6833X | 5818X | 2514 | 2615 | 3678A | 3678 | 2414 |
68 | 5624 | 6833 | 6833 | 5818 | 9950 | 2414 | 4976 | 3678X | Chassis & Attachments |
69 | 5835 | 6827 | 6886 | 5818A | Chassis & Attachments | Chassis & Attachments | 5186A | 3678A | 3727 |
70 | 5836 | 6886 | 2514 | 2514 | 5526 | 3727 | 5186 | 4976 | 3727A |
71 | 5830 | 2514 | 5813 | 9950 | 5526R | 3727A | Chassis & Attachments | 5186A | 3628 |
72 | 5832 | 5813 | 9950 | Chassis & Attachments | 1914R | 3628 | 3725X | 5186 | 2735 |
73 | 5832A | 9950 | Chassis & Attachments | 5526 | 5624 | 2735 | 3727 | Chassis & Attachments | 3722 |
74 | 5832G | Chassis & Attachments | 5526 | 5526R | 5835 | 3722 | 3727A | 3725X | 3722A |
75 | 5822 | 5526 | 5526R | 3619 | 5836 | 3722A | 3628 | 3627X | 3725 |
76 | 5822A | 5526R | 1914R | 5624 | 5830 | 3725 | 2735 | 3628 | 3629 |
77 | 5827 | 1914R | 5624 | 5835 | 5832 | 3625 | 3722 | 2735 | 5185 |
78 | 5827X | 5624 | 5835 | 5836 | 5832A | 3629 | 3722A | 3622 | 3723 |
79 | 5831 | 5835 | 5836 | 5830 | 5832G | 5185 | 3625 | 3622A | 3723A |
80 | 5831A | 5836 | 5830 | 5832 | 5822 | 3723 | 3725 | 3626 | 4974 |
81 | 5831G | 5830 | 5832 | 5832A | 5822A | 3723A | 3629 | 3629 | 3677A |
82 | 5823 | 5832 | 5832A | 5832G | 5827 | 4974 | 5185 | 3623 | 3677 |
83 | 5833 | 5832A | 5832G | 5822 | 5827X | 3677A | 3723 | 3623X | 3677X |
84 | 5833A | 5832G | 5822 | 5822A | 5831 | 3677 | 3723A | 3623A | 3678 |
85 | 5833G | 5822 | 5822A | 5827 | 5831A | 3677X | 4974 | 5185 | 3678X |
86 | 5625 | 5822A | 5827 | 5827X | 5831G | 3678 | 3677A | 4974 | 3678A |
87 | 5837 | 5827 | 5827X | 5831 | 5823 | 3678X | 3677 | 3677A | 4976 |
88 | 5185 | 5827X | 5831 | 5831A | 5833 | 3678A | 3677X | 3677 | 5186A |
89 | 4974 | 5831 | 5831A | 5831G | 5833A | 4976 | 3678 | 3677X | 5186 |
90 | 3677A | 5831A | 5831G | 5823 | 5833G | 5186A | 3678X | 3678 | Differential Components |
91 | 3677 | 5831G | 5823 | 5833 | 5625 | 5186 | 3678A | 3678X | 2381X |
92 | 3677X | 5823 | 5833 | 5833A | 5837 | Differential Components | 4976 | 3678A | 2382 |
93 | 3678 | 5833 | 5833A | 5833G | 5185 | 2381X | 5186A | 4976 | 2388X |
94 | 3678X | 5833A | 5833G | 5625 | 4974 | 2382 | 5186 | 5186A | 1647 |
95 | 3678A | 5833G | 5625 | 5837 | 3677A | 2388X | Differential Components | 5186 | Driveline Components |
96 | 4976 | 5625 | 5837 | 5185 | 3677 | 1647 | 2381X | Differential Components | 4628R |
97 | 5186A | 5837 | 5185 | 4974 | 3677X | Driveline Components | 2382 | 2381X | 4628X |
98 | 5186 | 5185 | 4974 | 3677A | 3678 | 4628R | 2388X | 2382 | 2751 |
99 | Differential Components | 4974 | 3677A | 3677 | 3678X | 4628X | 5169 | 2388X | 1651 |
100 | 2381X | 3677A | 3677 | 3677X | 3678A | 2751 | 1647 | 5169 | 3752 |
101 | 2382 | 3677 | 3677X | 3678 | 4976 | 1651 | Driveline Components | 1647 | 3652A |
102 | 2388X | 3677X | 3678 | 3678X | 5186A | 3652 | 5116 | Driveline Components | 3652G |
103 | 5169 | 3678 | 3678X | 3678A | 5186 | 3752 | 1675 | 5116 | 3652X |
104 | 1647 | 3678X | 3678A | 4976 | Differential Components | 3652A | 4628R | 4628R | 2754 |
105 | Driveline Components | 3678A | 4976 | 5186A | 2381X | 3652G | 4628X | 4628X | 2753X |
106 | 5116 | 4976 | 5186A | 5186 | 2382 | 3652X | 6828X | 6828X | Front Suspension & Steering |
107 | 4628R | 5186A | 5186 | Differential Components | 2388X | 2754 | 1951R | 1951R | 2728 |
108 | 4628X | 5186 | Differential Components | 2381X | 5169 | 2753X | 1953 | 1953 | 2545 |
109 | 6828X | Differential Components | 2381X | 2382 | 1647 | Front Suspension & Steering | 6752 | 6752 | 2530 |
110 | 6852X | 2381X | 2382 | 2388X | Driveline Components | 2728 | 6751 | 6751 | 2530A |
111 | 1953 | 2382 | 2388X | 5169 | 5116 | 4606 | 6850X | 6850X | 2432 |
112 | 6850X | 2388X | 5169 | 1647 | 4628R | 5114 | 1951 | 1951 | 2437 |
113 | 1951 | 5169 | 1647 | Driveline Components | 4628X | 2545 | 6757 | 6757 | 5525R |
114 | 3752 | 1647 | Driveline Components | 5116 | 6828X | 2530 | 3752 | 3752 | 5347 |
115 | 3652A | Driveline Components | 5116 | 4628R | 6852X | 2530A | 3652A | 3652A | 3646 |
116 | 3652G | 5116 | 6828X | 4628X | 1953 | 2432 | 3652G | 3652G | 3744 |
117 | 3652X | 4628R | 6852X | 6828X | 6850X | 2437 | 3652X | 3652X | 3639 |
118 | 2754 | 4628X | 6750 | 6852X | 1951 | 1942 | 2754 | 2754 | 3736 |
119 | 2753X | 6828X | 6751 | 6852R | 3752 | 5525R | 6753 | 6753 | 3636A |
120 | 6853X | 6852X | 6850X | 1953 | 3652A | 2742 | 2753X | 2753X | 3636G |
121 | Front Suspension & Steering | 6852R | 6757 | 6750 | 3652G | 5347 | 6853X | 6853X | 3636X |
122 | 3637 | 1953 | 3752 | 6751 | 3652X | 2742X | Front Suspension & Steering | Front Suspension & Steering | 2531X |
123 | 2728 | 6750 | 3652A | 6850X | 2754 | 3744 | 3637 | 3637 | 2640 |
124 | 5116 | 6751 | 3652G | 1951 | 2753X | 3639 | 2728 | 2728 | 3640 |
125 | 2545 | 6850X | 3652X | 6757 | 6853X | 3636 | 5116 | 5116 | 2537 |
126 | 2530 | 1951 | 2754 | 3752 | Front Suspension & Steering | 3736 | 2545 | 2545 | 2532 |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | ||
2 | 1926 | 1926 | 1926 | 1926 | ||
3 | 5813 | 6821 | 6821 | 6825X | ||
4 | 9950 | 6825 | 6825 | 6826 | ||
5 | Chassis & Attachments | 6825X | 6825X | 6811X | ||
6 | 5526 | 6826 | 6826 | 6833X | ||
7 | 5836 | 6886 | 2514 | 2514 | ||
8 | 5830 | 2514 | 5813 | 9950 | ||
9 | 5832 | 5813 | 9950 | Chassis & Attachments | ||
10 | 5832A | 9950 | Chassis & Attachments | 5526 | ||
11 | 5832G | Chassis & Attachments | 5526 | 5526R | ||
12 | 5822 | 5526 | 5526R | 3619 | ||
13 | 4976 | 5625 | 5837 | 3678A | ||
14 | 5186A | Differential Components | 5185 | 4976 | ||
15 | 5186 | 2381X | 4974 | 5186A | ||
16 | Differential Components | 2382 | 3677A | 5186 | ||
17 | 2381X | 2388X | 5186A | Differential Components | ||
18 | 5169 | 5186 | 2381X | |||
19 | 1647 | Differential Components | 2382 | |||
20 | 2381X | 2388X | ||||
21 | 2382 | 5169 | ||||
22 | 2388X | 1647 | ||||
23 | 5169 | |||||
Before |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | ||
2 | 1926 | 1926 | 1926 | 1926 | ||
3 | 5813 | 6821 | 6821 | 6825X | ||
4 | 9950 | 6825 | 6825 | 6826 | ||
5 | 6825X | 6825X | 6811X | |||
6 | 6826 | 6826 | 6833X | |||
7 | 6886 | 2514 | 2514 | |||
8 | 2514 | 5813 | 9950 | |||
9 | 5813 | 9950 | ||||
10 | 9950 | |||||
11 | Chassis & Attachments | Chassis & Attachments | Chassis & Attachments | Chassis & Attachments | ||
12 | 5526 | 5526 | 5526 | 5526 | ||
13 | 5836 | 5625 | 5526R | 5526R | ||
14 | 5830 | 5837 | 3619 | |||
15 | 5832 | 5185 | 3678A | |||
16 | 5832A | 4974 | 4976 | |||
17 | 5832G | 3677A | 5186A | |||
18 | 5822 | 5186A | 5186 | |||
19 | 4976 | 5186 | ||||
20 | 5186A | |||||
21 | 5186 | |||||
22 | Differential Components | Differential Components | Differential Components | Differential Components | ||
23 | 2381X | 2381X | 2381X | 2381X | ||
24 | 5169 | 2382 | 2382 | 2382 | ||
25 | 1647 | 2388X | 2388X | 2388X | ||
26 | 5169 | 5169 | ||||
27 | 1647 | |||||
After |
Two further questions ..
4. Actually relates to Q2 above - thinking about how to identify heading cells. Perhaps another possibility is that there is a fixed list of headings available?
5. Approximately how many rows of data might there be in your real data set?
6. What is this heading?Q2: Data is 5 or less characters, and all but one heading is over 5 characters long
That certainly makes things more difficult, but how much more difficult depends partly on ..Q3: Not all headings appear in all columns
Excel Workbook | ||||
---|---|---|---|---|
B | C | |||
1 | Heading 1 | Heading 1 | ||
2 | data | data | ||
3 | data | Heading 3 | ||
4 | Heading 2 | data | ||
5 | data | data | ||
6 | Heading 3 | data | ||
7 | data | Heading 2 | ||
8 | data | data | ||
9 | data | data | ||
Sample |
6. What is this heading?
That certainly makes things more difficult, but how much more difficult depends partly on ..
7. For the headings that do exist, are they always in a fixed order? For example, could you have this where the headings are not in the same order?
Unfortunately, even one out of order would spoil the plan I had in mind. However, let's see how this one goes.Q7: there are a few places where headings are out of order, not many
Sub Align_Headings()
Dim a As Variant, b As Variant, aHdrs As Variant
Dim lr As Long, i As Long, j As Long, k As Long, r As Long, x As Long, y As Long, rws As Long, cols As Long
Dim CurrHdr As String
Const sShortHdr As String = "Fuel"
Const sDataCols As String = "B:E" '"B:AQ"
Const lFirstRow As Long = 3
Const sHdrs As String = "Body & Accessories|Chassis & Attachments|Differential Components|" & _
"Fuel|Driveline Components|Front Suspension & Steering|" & _
"Bearings"
aHdrs = Split(sHdrs, "|")
lr = Columns(sDataCols).Find(What:="*", After:=Range(sDataCols).Cells(1, 1), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row + 1
With Range(sDataCols).Resize(lr - lFirstRow + 1).Offset(lFirstRow - 1)
a = .Value
rws = UBound(a, 1)
cols = UBound(a, 2)
ReDim b(1 To rws * cols, 1 To cols)
For x = 0 To UBound(aHdrs)
CurrHdr = aHdrs(x)
k = k + r + 1
r = 0
For j = 1 To cols
b(k, j) = CurrHdr
y = 0
i = 1
Do Until a(i, j) = CurrHdr Or i = rws
i = i + 1
Loop
If i < rws Then
i = i + 1
Do While Len(a(i, j)) < 6 And i < rws And a(i, j) <> sShortHdr
If Len(a(i, j)) > 0 Then
y = y + 1
If y > r Then r = y
b(k + y, j) = a(i, j)
End If
i = i + 1
Loop
End If
Next j
Next x
End With
Sheets.Add After:=ActiveSheet
With ActiveSheet
.Range("A1").Resize(k + r, cols).Value = b
With .UsedRange.EntireColumn
.AutoFit
.HorizontalAlignment = xlCenter
End With
End With
End Sub
Excel Workbook | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
1 | ||||||
2 | ||||||
3 | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | ||
4 | 1926 | 1926 | 1926 | 1926 | ||
5 | 5813 | 6821 | 6821 | 6825X | ||
6 | 9950 | 6825 | 6825 | Fuel | ||
7 | Driveline Components | 6825X | Fuel | 6811X | ||
8 | 5526 | 6826 | 6826 | 6833X | ||
9 | 5836 | 6886 | 2514 | 2514 | ||
10 | 5830 | 2514 | 5813 | 9950 | ||
11 | Fuel | 5813 | 9950 | Driveline Components | ||
12 | 5832A | 9950 | Driveline Components | 5526 | ||
13 | 5832G | Driveline Components | 5526 | 5526R | ||
14 | 5822 | 5526 | 5526R | 3619 | ||
15 | 4976 | 5625 | 5837 | 3678A | ||
16 | 5186A | Differential Components | 5185 | 4976 | ||
17 | 5186 | 2381X | 4974 | 5186A | ||
18 | Differential Components | 2382 | 3677A | 5186 | ||
19 | 2381X | 2388X | 5186A | Differential Components | ||
20 | 5169 | 5186 | 2381X | |||
21 | 1647 | Differential Components | 2382 | |||
22 | 2381X | 2388X | ||||
23 | 2382 | 5169 | ||||
24 | 2388X | 1647 | ||||
25 | 5169 | |||||
26 | ||||||
Before |
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Body & Accessories | Body & Accessories | Body & Accessories | Body & Accessories | ||
2 | 1926 | 1926 | 1926 | 1926 | ||
3 | 5813 | 6821 | 6821 | 6825X | ||
4 | 9950 | 6825 | 6825 | |||
5 | 6825X | |||||
6 | 6826 | |||||
7 | 6886 | |||||
8 | 2514 | |||||
9 | 5813 | |||||
10 | 9950 | |||||
11 | Chassis & Attachments | Chassis & Attachments | Chassis & Attachments | Chassis & Attachments | ||
12 | Differential Components | Differential Components | Differential Components | Differential Components | ||
13 | 2381X | 2381X | 2381X | 2381X | ||
14 | 5169 | 2382 | 2382 | 2382 | ||
15 | 1647 | 2388X | 2388X | 2388X | ||
16 | 5169 | 5169 | ||||
17 | 1647 | |||||
18 | Fuel | Fuel | Fuel | Fuel | ||
19 | 5832A | 6826 | 6811X | |||
20 | 5832G | 2514 | 6833X | |||
21 | 5822 | 5813 | 2514 | |||
22 | 4976 | 9950 | 9950 | |||
23 | 5186A | |||||
24 | 5186 | |||||
25 | Driveline Components | Driveline Components | Driveline Components | Driveline Components | ||
26 | 5526 | 5526 | 5526 | 5526 | ||
27 | 5836 | 5625 | 5526R | 5526R | ||
28 | 5830 | 5837 | 3619 | |||
29 | 5185 | 3678A | ||||
30 | 4974 | 4976 | ||||
31 | 3677A | 5186A | ||||
32 | 5186A | 5186 | ||||
33 | 5186 | |||||
34 | Front Suspension & Steering | Front Suspension & Steering | Front Suspension & Steering | Front Suspension & Steering | ||
35 | Bearings | Bearings | Bearings | Bearings | ||
36 | ||||||
After |