Aligning identical data from two columns that have different data

[jmd0103]

I have two columns:
[TABLE="width: 195"]
<TBODY>[TR]
[TD]red</SPAN>
[/TD]
[TD]red</SPAN>
[/TD]
[/TR]
[TR]
[TD]yellow</SPAN>
[/TD]
[TD]blue</SPAN>
[/TD]
[/TR]
[TR]
[TD]teal</SPAN>
[/TD]
[TD]yellow</SPAN>
[/TD]
[/TR]
[TR]
[TD]black </SPAN>
[/TD]
[TD]black </SPAN>
[/TD]
[/TR]
[TR]
[TD]green</SPAN>
[/TD]
[TD]green</SPAN>
[/TD]
[/TR]
[TR]
[TD]pink</SPAN>
[/TD]
[TD]pink</SPAN>
[/TD]
[/TR]
[TR]
[TD]orange</SPAN>
[/TD]
[TD]grey </SPAN>
[/TD]
[/TR]
[TR]
[TD]grey </SPAN>
[/TD]
[TD]white</SPAN>
[/TD]
[/TR]
[TR]
[TD]brown</SPAN>
[/TD]
[TD]brown</SPAN>
[/TD]
[/TR]
[TR]
[TD]beige</SPAN>
[/TD]
[TD]purple</SPAN>
[/TD]
[/TR]
[TR]
[TD]purple</SPAN>
[/TD]
[TD]silver</SPAN>
[/TD]
[/TR]
[TR]
[TD]silver</SPAN>
[/TD]
[TD]maroon</SPAN>
[/TD]
[/TR]
[TR]
[TD]maroon</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Column 1 has some data that column 2 does not have, as column 2 has some data that column 1 does not have. Both columns are in the same order however, and they must STAY in this same order. I do not wish to put it in alphabetical order. What I would like to get out of a macro is:

[TABLE="width: 195"]
<TBODY>[TR]
[TD]red</SPAN>
[/TD]
[TD]red</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]blue</SPAN>
[/TD]
[/TR]
[TR]
[TD]yellow</SPAN>
[/TD]
[TD]yellow</SPAN>
[/TD]
[/TR]
[TR]
[TD]teal </SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]black </SPAN>
[/TD]
[TD]black </SPAN>
[/TD]
[/TR]
[TR]
[TD]green</SPAN>
[/TD]
[TD]green </SPAN>
[/TD]
[/TR]
[TR]
[TD]pink</SPAN>
[/TD]
[TD]pink</SPAN>
[/TD]
[/TR]
[TR]
[TD]orange</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grey </SPAN>
[/TD]
[TD]grey </SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]white</SPAN>
[/TD]
[/TR]
[TR]
[TD]brown</SPAN>
[/TD]
[TD]brown</SPAN>
[/TD]
[/TR]
[TR]
[TD]beige</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]purple</SPAN>
[/TD]
[TD]purple</SPAN>
[/TD]
[/TR]
[TR]
[TD]silver</SPAN>
[/TD]
[TD]silver</SPAN>
[/TD]
[/TR]
[TR]
[TD]maroon</SPAN>
[/TD]
[TD]maroon</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

It needs to be able to recognize the difference between having a value missing from column 1 versus having a value missing from column 2. What I am having trouble with is after finding a non-match, I need to scan BOTH columns to see whether the value is missing from 1 or 2, and shift the other column accordingly.

Any help would be GREATLY appreciated.

Thank you!

 

[jeffmb]

This should do it for you:

Sub sync_columns()
Dim rowA As Integer
Dim rowB As Integer
Dim rng As Range
    
    rowA = 1
    Do While Not IsEmpty(Range("A" & rowA))
        With Worksheets("Sheet1").Range("B:B")
            Set rng = .Find(Range("A" & rowA).Value, LookIn:=xlValues)
            If Not rng Is Nothing Then
                rowB = rng.Row
                If rowB > rowA Then
                    Range("A" & rowA & ":A" & rowB - 1).Select
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    rowA = rowB
                ElseIf rowA > rowB Then
                    Range("B" & rowB & ":B" & rowA - 1).Select
                    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                End If
            End If
            rowA = rowA + 1
        End With
    Loop
    
End Sub

 

[jmd0103]

Thank You THANK YOU!