Aligning 2 columns in Excel

Question

I have 2 columns with data, sorted alpha-numeric. The larger is about 300 entries, but could be a lot bigger. The smaller, less than half the number of entries, contains some of the same values as the larger one. 

Is it possible to align the smaller column such that where the two values are the same they are on the same row?

Sometimes the smaller column has additional values not in the larger one. In this case is it possible to insert an additional row in the larger column at the appropriate alpha-numeric position?

Thank you in advance.

Answer 1

Do you want to compare more than two columns, or do you want other columns to shift down along with the first or second column?

Answer 2

HansV,

Update to first reply.

I tried your macro on another set of data and your macro works brilliantly. The data by the way are mutation positions on the Y-chromosome that we get from some DNA tests and this is to compare the results from two persons. Is it possible to include further data columns to the macro?

Great work Hans,

Svein

Answer 3

You could run another macro for that. Select the range in which some or all of the cells have a trailing comma, then run this:

Sub DeleteTrailingComma()

    Dim cel As Range

    Application.ScreenUpdating = False

    For Each cel In Selection

        If Right(cel.Value, 1) = "," Then

            cel.Value = Left(cel.Value, Len(cel.Value) - 1)

        End If

    Next cel

    Application.ScreenUpdating = True

End Sub

Answer 4

HansV,

Thank you very much - that was a very fast response.

Having never run a Macro before I had to do a crash course in "How to" and I still don't know if I really understand, but your Macro worked on my columns!

The only problem is that in converting one of the data files to a column it included the "comma" (,) at the end of the data, i.e. none of the rows are the same!! I now need to figure out how to delete the trailing comma!

Thank you HansV

Answer 5

Here is a macro you can use. Modify the constants at the beginning to match your situation. For example, if the data begin in row 2 instead of row 1, change

    Const FirstRow = 1

to

    Const FirstRow = 2

To be on the safe side, make a copy of the worksheet or workbook before running the macro.

Sub Zipper()

    Const Col1 = 1 ' column A

    Const Col2 = 2 ' column B

    Const FirstRow = 1

    Dim CurRow As Long

    Application.ScreenUpdating = False

    CurRow = FirstRow

    Do

        If Cells(CurRow, Col1).Value <> "" And Cells(CurRow, _

                Col1).Value < Cells(CurRow, Col2).Value Then

            Cells(CurRow, Col2).Insert Shift:=xlShiftDown

        ElseIf Cells(CurRow, Col2).Value <> "" And Cells(CurRow, _

                Col2).Value < Cells(CurRow, Col1).Value Then

            Cells(CurRow, Col1).Insert Shift:=xlShiftDown

        End If

        CurRow = CurRow + 1

    Loop Until Cells(CurRow, Col1).Value = "" And _

        Cells(CurRow, Col2).Value = ""

    Application.ScreenUpdating = True

End Sub