It's a Match!

[goodpasture]

I have 2 columns (A and B) and each has 3 or 4 digit numbers all mixed up. Basically I need to be able to match them up (if there is a match) and I am going to delete them if they are both in A and B but I need to know which ones are ONLY in A and also which ones are ONLY in B which is what should be left...right???

Thanks,

Jonathan

[Paul Sheppard]

I have 2 columns (A and B) and each has 3 or 4 digit numbers all mixed up. Basically I need to be able to match them up (if there is a match) and I am going to delete them if they are both in A and B but I need to know which ones are ONLY in A and also which ones are ONLY in B which is what should be left...right???

Thanks,

Jonathan

Hi Jonathan

Try this in column C, =IF(COUNTIF($A$1:$A$10,B1)=0,"No Match","Match"), change the range A1:A10 to suit your data, it will identify all matches so if column A contains 1234 twice and 1234 is also in column B both will show as a match

[goodpasture]

But isn't there a function in Excel called Match? I can't find much on it but I seen it listed in a function listing. Does anyone know anything about this?

[goodpasture]

I guess nevermind on that last post about the match. The IFCOUNT actualyl seems to work but something further I think I'm going to need is that I need to be able to sort them so the matches are together. I should have a group where A and B are together (matched) and then a group where there is only stuff in A and then only stuff in B. thinking about it, is there maybe a way to just find out what's matching and get rid of it and then what should be left is stuff only in A and tehn stuff only in B.

[Paul Sheppard]

I guess nevermind on that last post about the match. The IFCOUNT actualyl seems to work but something further I think I'm going to need is that I need to be able to sort them so the matches are together. I should have a group where A and B are together (matched) and then a group where there is only stuff in A and then only stuff in B. thinking about it, is there maybe a way to just find out what's matching and get rid of it and then what should be left is stuff only in A and tehn stuff only in B.

Hi goodpasture

Try this macro it sorts the data to align the matches in the 2 columns, it assumes that Row 1 is a Header row so leaves that row alone, your data should therefore start in row 2, it also assumes data is in columns A and B

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 1 ' columns B only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

[goodpasture]

Thanks Paul...

Now how would I add column D,E, and F to follow along with B with this code?

Thanks again,

Jonathan