Macro that aligns 8 columns

[llangid]

I am sure this can be done with a macro but I not having any luck at all creating one or finding one so I am hoping someone here can help me.


I have 8 columns that need to be aligned and make one master column that four of the columns align to. Let’s say column B, D F and H have the numbers in them that need to be aligned in rows with each other and columns C, E, G, and H each need to stay with the preceding column. So columns BC, DE, FG, and HI need to stay together as the alignment happens.



There are common numbers between columns B, D, F, and H that need to be matched and there could be duplicates in any one of the groups of columns (group BC, group DE, group FG, and group HI) and the column groups are of different lengths .


As the columns are aligned the number that is put into column A becomes the master column for all the numbers in the rows.


Thanks in advance for your help.

 

[mirabeau]

Hi,

Welcome to MrExcel.

Try the following macro code on some test data and see if it works for you.

It gives the aligned rows in sorted order, coz I wasn't sure about your master column. Can easily be modified tho' if you specify how you want it.

Sub lineemup()
Dim nr As Long, j As Long, k As Long
Application.ScreenUpdating = False
nr = Range("B:I").Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
For j = 2 To 8 Step 2
    Cells(1, j).Resize(nr).Sort Cells(1, j), 1
Next j
Do
k = k + 1
With Cells(1, Columns.Count)
    .Resize(8).ClearContents
For j = 2 To 8 Step 2
    .Offset(j - 1) = Cells(k, j).Value
Next j
    .Resize(8).Sort .Resize(1), 1
For j = 2 To 8 Step 2
    If Len(Cells(k, j)) > 0 And _
        Cells(k, j) <> .Value Then Cells(k, j).Resize(, 2).Insert xlDown
Next j
End With
Loop Until Application.CountA(Range("B1").Resize(, 8).Offset(k)) = 0
Application.ScreenUpdating = True
End Sub

 

[llangid]

Mirabeau

The code works perfectly

For the master column I want the number of whatever is in that row. So if in row 1 column b is empty column d has number 12543 in it and column f and h are empty then column A (master column) would also has 12543 in it. Then that would just be repeated all the way down sheet for every row.

Thank you very much I really appreciate your help.

 

[mirabeau]

OK. Glad that it works and does what you want.

I'm still not clear about what the master column is supposed to do though.

However, maybe you can organize whatever you want yourself.

 

[llangid]

Mirabeau

Yea adding the master column up front is a simple Min formula and I can easily do that.

Once again thanks for your help I really appreciate it.