Matching 2 columns

[Lynda]

Sorry folks but I am having a brain dead moment because I know I have done
this before. I have three columns of data, in column A I have a list of
names, in column B I have another list of names that are matched up with the
dates in column C. I want to be able to match up the names in column B with
the names in column A by forcing the names in column B down until they find a
match in column A. Column A has more names than column B and not all names
have a match. The names in column A that don’t have a match will be deleted.
A B C
Ashton, Bobbie Brown, Joe 1/2/1967
Brown, Joe Jones, Penny 8/7/1972
Crisp, Jason Smith, John 12/6/1981
Smith, John

Hope this makes sense. Thanks in advance.
Cheers
Lynda

 

[Jacob Skaria]

You don't need the temporary Col C. If you are looking to get data as shown
below. ColC retrieves the 1st matching record of John ColD retreives the
second and so on. Please note that this is an array formula. Within the cell
in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

Try this formula in C1 and copy across to D1, E1 etc;

=INDEX($A:$A,SMALL(IF($B$1:$B$1000<>$B1,"",($B$1:$B$1000=$B1)*ROW($B$1:$B$1000)),COLUMN(A1)))

Col A Col B Col C Col D Col E
A John A B C
E Mary
F Serra
B John
G Phil
H Ben
C John


If this post helps click Yes

 

[Jacob Skaria]

Hi Lynda

Please ignore the previous post.....For your query please try the below
macro which use ColD as a temporary column and try to find a match in ColA.
Once a match is done for all entries in ColB ColA is replaced with ColD. Does
that suit your requirement?

Sub MatchAndDelete()
Dim lngRow As Long, rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Trim(Range("B" & lngRow)) <> "" Then
If WorksheetFunction.CountIf(rngTemp, Range("B" & lngRow)) > 0 Then
Range("D" & lngRow) = Range("B" & lngRow).Text
End If
End If
Next
Range("A:A") = Range("D").Value
Range("D").ClearContents
End Sub



If this post helps click Yes

 

[Lynda]

Thank you Jacob, it works a treat.

Cheers
Lynda

Hi Lynda

Please ignore the previous post.....For your query please try the below
macro which use ColD as a temporary column and try to find a match in ColA.
Once a match is done for all entries in ColB ColA is replaced with ColD. Does
that suit your requirement?

Sub MatchAndDelete()
Dim lngRow As Long, rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Trim(Range("B" & lngRow)) <> "" Then
If WorksheetFunction.CountIf(rngTemp, Range("B" & lngRow)) > 0 Then
Range("D" & lngRow) = Range("B" & lngRow).Text
End If
End If
Next
Range("A:A") = Range("D").Value
Range("D").ClearContents
End Sub



If this post helps click Yes