align data to rows by heading?

Perodin

Board Regular
Joined
Sep 16, 2014
Messages
54
I have headings across many columns that are staggered, is there a way to select or reference the headings and get them to snap to a single row (moving all data below with it) each column has 20 or so headings.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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

I want to snap headings to the same rows even if I have to select all the same ones manually, but I need data below them to carry.

Excel 2007
BCDEFGHIJ
44Body & AccessoriesBody & AccessoriesBody & AccessoriesBody & AccessoriesBody & AccessoriesBody & AccessoriesBody & AccessoriesBody & AccessoriesBody & Accessories
45192619261926192619261926192619261926
46172617261726172617261726172617261726
47393539353935393539351834183418341834
48393439343934393439341815393439341815
491914R1914R1914R36191914R24171914R1914R2417
50181518151815181518152434361436142418
51681168856885581458142417X181536192514
526817688768875814A5814A2514371418152413R
536831581458145814R5814R9950371737149950
5468185814A5814A5814X5814X3931361737175185
5568205814R5814R6811681151853617R36174974
5668215814X5814X6817681749743617X3617R3677A
57682568116811683168313677A25143617X3677
586825X68176817681868183677371625143677X
59682668316831682068203677X995036163678
606811X68186818682168213678518599503678X
61251468206820682568253678X497439313678A
625813682168216825X6825X3678A3677A51854976
63995068256825682668264976367749745186A
64Chassis & Attachments6825X6825X6811X6811X5186A3677X3677A5186
655526682668266833X6833X5186367836772712
665526R6811X6811X6833683327123678X3677X2615
671914R6833X6833X5818X251426153678A36782414
6856246833683358189950241449763678XChassis & Attachments
695835682768865818AChassis & AttachmentsChassis & Attachments5186A3678A3727
70583668862514251455263727518649763727A
7158302514581399505526R3727AChassis & Attachments5186A3628
72583258139950Chassis & Attachments1914R36283725X51862735
735832A9950Chassis & Attachments5526562427353727Chassis & Attachments3722
745832GChassis & Attachments55265526R583537223727A3725X3722A
75582255265526R361958363722A36283627X3725
765822A5526R1914R562458303725273536283629
7758271914R5624583558323625372227355185
785827X5624583558365832A36293722A36223723
7958315835583658305832G518536253622A3723A
805831A58365830583258223723372536264974
815831G583058325832A5822A3723A362936293677A
82582358325832A5832G58274974518536233677
8358335832A5832G58225827X3677A37233623X3677X
845833A5832G58225822A583136773723A3623A3678
855833G58225822A58275831A3677X497451853678X
8656255822A58275827X5831G36783677A49743678A
87583758275827X583158233678X36773677A4976
8851855827X58315831A58333678A3677X36775186A
89497458315831A5831G5833A497636783677X5186
903677A5831A5831G58235833G5186A3678X3678Differential Components
9136775831G58235833562551863678A3678X2381X
923677X582358335833A5837Differential Components49763678A2382
93367858335833A5833G51852381X5186A49762388X
943678X5833A5833G56254974238251865186A1647
953678A5833G562558373677A2388XDifferential Components5186Driveline Components
964976562558375185367716472381XDifferential Components4628R
975186A5837518549743677XDriveline Components23822381X4628X
985186518549743677A36784628R2388X23822751
99Differential Components49743677A36773678X4628X51692388X1651
1002381X3677A36773677X3678A2751164751693752
101238236773677X367849761651Driveline Components16473652A
1022388X3677X36783678X5186A36525116Driveline Components3652G
103516936783678X3678A51863752167551163652X
10416473678X3678A4976Differential Components3652A4628R4628R2754
105Driveline Components3678A49765186A2381X3652G4628X4628X2753X
106511649765186A518623823652X6828X6828XFront Suspension & Steering
1074628R5186A5186Differential Components2388X27541951R1951R2728
1084628X5186Differential Components2381X51692753X195319532545
1096828XDifferential Components2381X23821647Front Suspension & Steering675267522530
1106852X2381X23822388XDriveline Components2728675167512530A
111195323822388X5169511646066850X6850X2432
1126850X2388X516916474628R5114195119512437
113195151691647Driveline Components4628X2545675767575525R
11437521647Driveline Components51166828X2530375237525347
1153652ADriveline Components51164628R6852X2530A3652A3652A3646
1163652G51166828X4628X195324323652G3652G3744
1173652X4628R6852X6828X6850X24373652X3652X3639
11827544628X67506852X19511942275427543736
1192753X6828X67516852R37525525R675367533636A
1206853X6852X6850X19533652A27422753X2753X3636G
121Front Suspension & Steering6852R675767503652G53476853X6853X3636X
12236371953375267513652X2742XFront Suspension & SteeringFront Suspension & Steering2531X
123272867503652A6850X27543744363736372640
124511667513652G19512753X3639272827283640
12525456850X3652X67576853X3636511651162537
1262530195127543752Front Suspension & Steering3736254525452532

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Printable Lists
 
Upvote 0
That's still a pretty large sample and you didn't show "before" and "after" as I requested. I assume your screen shot is a "before" one.

1. Here's a cut-down version of that sample and my guess of what the "after" screen shot might look like. Please confirm this or provide the actual "after" screen shot for this smaller data.

2. Can I assume that with your real data all the headings are more than 5 characters and all the non-headings are 5 or less characters?
If not, is there something unique about headings or non-headings that would save having to select them manually?

3. If a heading appears in one column, can we be certain that that heading appears somewhere in every column?

Excel Workbook
BCDE
1Body & AccessoriesBody & AccessoriesBody & AccessoriesBody & Accessories
21926192619261926
35813682168216825X
49950682568256826
5Chassis & Attachments6825X6825X6811X
65526682668266833X
75836688625142514
85830251458139950
9583258139950Chassis & Attachments
105832A9950Chassis & Attachments5526
115832GChassis & Attachments55265526R
12582255265526R3619
134976562558373678A
145186ADifferential Components51854976
1551862381X49745186A
16Differential Components23823677A5186
172381X2388X5186ADifferential Components
18516951862381X
191647Differential Components2382
202381X2388X
2123825169
222388X1647
235169
Before




Excel Workbook
BCDE
1Body & AccessoriesBody & AccessoriesBody & AccessoriesBody & Accessories
21926192619261926
35813682168216825X
49950682568256826
56825X6825X6811X
6682668266833X
7688625142514
8251458139950
958139950
109950
11Chassis & AttachmentsChassis & AttachmentsChassis & AttachmentsChassis & Attachments
125526552655265526
13583656255526R5526R
14583058373619
15583251853678A
165832A49744976
175832G3677A5186A
1858225186A5186
1949765186
205186A
215186
22Differential ComponentsDifferential ComponentsDifferential ComponentsDifferential Components
232381X2381X2381X2381X
245169238223822382
2516472388X2388X2388X
2651695169
271647
After
 
Upvote 0
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?
 
Upvote 0
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?

I apologize for not getting the after too.
Q1: Your after is what I am trying to achieve.
Q2: Data is 5 or less characters, and all but one heading is over 5 characters long (I can rename this heading using the find and replace if it needs more characters). There is nothing unique about them that I can think of.
Q3: Not all headings appear in all columns (could I drop the fixed list in at the end of each column to solve problems, then delete after the snap?).
Q4: I do have or can easily create a fixed list of headings (there are 41 in total).
Q5: The data spans B3:AQ579 so 586 rows.
 
Last edited:
Upvote 0
Q2: Data is 5 or less characters, and all but one heading is over 5 characters long
6. What is this heading?


Q3: Not all headings appear in all columns
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

Excel Workbook
BC
1Heading 1Heading 1
2datadata
3dataHeading 3
4Heading 2data
5datadata
6Heading 3data
7dataHeading 2
8datadata
9datadata
Sample
 
Upvote 0
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?

Q6: the heading is Fuel
Q7: there are a few places where headings are out of order, not many
 
Upvote 0
Q7: there are a few places where headings are out of order, not many
Unfortunately, even one out of order would spoil the plan I had in mind. However, let's see how this one goes.

Test in a copy of your workbook.

This only allows for one "short" (< 6 characters) heading. If it turns out there are more, we might do some Find/Replace but could still be done in the code.

The code will list every heading in every column of the target columns with any data below that.

The order of the items in the resultant table is determined by their order in the 'Const sHdrs As String' line in the code. You would need to extend/adjust this line to include all the headers in the order you want them.
Be sure to include the "|" character between each item & at the end of each row, except the last. Just follow the pattern I have set.

You'll also need to amend the 'Const sDataCols As String' line to represent the columns you want as I have left it with the smaller range that I tested and have shown results for.

The code puts the results on a new worksheet but if you wanted it elsewhere, shouldn't be a problem.

Rich (BB code):
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

For my sample data:

Excel Workbook
BCDE
1
2
3Body & AccessoriesBody & AccessoriesBody & AccessoriesBody & Accessories
41926192619261926
55813682168216825X
6995068256825Fuel
7Driveline Components6825XFuel6811X
85526682668266833X
95836688625142514
105830251458139950
11Fuel58139950Driveline Components
125832A9950Driveline Components5526
135832GDriveline Components55265526R
14582255265526R3619
154976562558373678A
165186ADifferential Components51854976
1751862381X49745186A
18Differential Components23823677A5186
192381X2388X5186ADifferential Components
20516951862381X
211647Differential Components2382
222381X2388X
2323825169
242388X1647
255169
26
Before



Excel Workbook
ABCD
1Body & AccessoriesBody & AccessoriesBody & AccessoriesBody & Accessories
21926192619261926
35813682168216825X
4995068256825
56825X
66826
76886
82514
95813
109950
11Chassis & AttachmentsChassis & AttachmentsChassis & AttachmentsChassis & Attachments
12Differential ComponentsDifferential ComponentsDifferential ComponentsDifferential Components
132381X2381X2381X2381X
145169238223822382
1516472388X2388X2388X
1651695169
171647
18FuelFuelFuelFuel
195832A68266811X
205832G25146833X
21582258132514
22497699509950
235186A
245186
25Driveline ComponentsDriveline ComponentsDriveline ComponentsDriveline Components
265526552655265526
27583656255526R5526R
28583058373619
2951853678A
3049744976
313677A5186A
325186A5186
335186
34Front Suspension & SteeringFront Suspension & SteeringFront Suspension & SteeringFront Suspension & Steering
35BearingsBearingsBearingsBearings
36
After
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top