Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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
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


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)
        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 lngRow
    [I1].Resize(UBound(Z, 1), UBound(Z, 2)) = Z
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .