# Showing tables with common and uncommon values with excel

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

-
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

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