Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to align rows of two different tables in a column so that absent rows appear as blank rows on each of the tables. So for example, if I have the following tables:

    A   4   9                   A   4   8
    B   3   7                   C   3   9
    D   4   2                   E   7   2
    F   4   2       and         F   1   3

And I want it to show me which columns are common on the first field and which aren't, my resulting tables should look like:

    A   4   9                   A   4   8
    B   3   7                   
                                C   3   9
    D   4   2                   
                                E   7   2
    F   4   2       and         F   1   3

How can I achieve this with Excel?

Ted

share|improve this question
    
This is probably more so a stackoveflow question as any steped method will be relatively complex in terms of working column "flagging" formula and cell insertions. –  brettdj Sep 25 '11 at 11:00
add comment

1 Answer

up vote 1 down vote accepted

If your two tables were in the same sheet, starting at A1 for table 1 and contiuning to column C (C6 in your sample), E1 for table 2 continuing to column G (g6 in your sample) then this array sorting code dumps you designed output to a new table starting at I1

hth

Sub LineEmStackEm()
    Dim X
    Dim Y
    Dim Z
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCnt As Long
    Dim lngMiss As Long
    X = Range([a1], Cells(Rows.Count, "c").End(xlUp))
    Y = Range([e1], Cells(Rows.Count, "g").End(xlUp))
    ReDim Z(1 To 2 * UBound(Y, 1), 1 To 6)
    For lngRow = 1 To UBound(Y)
        For lngCnt = 1 To UBound(X, 2)
            Z(lngRow + lngMiss, lngCnt) = X(lngRow, lngCnt)
        Next
        If X(lngRow, 1) <> Y(lngRow, 1) Then lngMiss = lngMiss + 1
        For lngCnt = 1 To UBound(Y, 2)
            Z(lngRow + lngMiss, lngCnt + UBound(X, 2)) = Y(lngRow, lngCnt)
        Next
    Next lngRow
    [I1].Resize(UBound(Z, 1), UBound(Z, 2)) = Z
End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.