Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


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.