It's a Match!

G

goodpasture

I have 2 columns (A and B) and each has 3 or 4 digit numbers all mixe
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 t
know which ones are ONLY in A and also which ones are ONLY in B which i
what should be left...right???

Thanks,

Jonatha
 
P

Paul Sheppard

goodpasture said:
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
Click to expand...

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
 
G

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?
 
G

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.
 
P

Paul Sheppard

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

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, you
data should therefore start in row 2, it also assumes data is i
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 Su
 
G

goodpasture

Thanks Paul...

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

Thanks again,

Jonathan
 
Top