You could use a macro.
But this destroys data in place. Do it against a copy of your worksheet or
don't save it if it isn't right when you're done:
Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim res As Variant
Dim wks As Worksheet
Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
For iRow = FirstRow To LastRow
If .Cells(iRow, "b").Value = .Cells(iRow, "c").Value Then
'do nothing--already match
Else
res = Application.Match(CLng(.Cells(iRow, "b").Value), _
.Range(.Cells(iRow, "c"), _
.Cells(.Rows.Count, "c").End(xlUp)), 0)
If IsError(res) Then
MsgBox "Bad data" & vbNewLine & _
"There is no match in C for: " _
& .Cells(iRow, "B").Text
Exit Sub
Else
.Cells(iRow, "c").Resize(res - 1, 2).Delete shift:=xlUp
End If
End If
Next iRow
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Select your range of cells
click on Tools|macro|macros...
click on the macro name (testme01--but you could rename it to something
meaningful!)
and then click run.
