Is This Possible? - Data matching

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gilly - 12 May 2006 15:37 GMT

I have a 3 columns of data:

INFORMATION | TYPE | MATCH
Jim Firstname
Jack Firstname
Tom Firstname
Jones Lastname
Smith Lastname
Davis Lastname

I have a list of data I need to enter into the MATCH column. I would like a
function in the MATCH column that automatically matches the data I enter in
the MATCH column with matching data in the INFORMATION column.

For example, if I enter the following data into the MATCH column

Jack
Smith
Davis

The MATCH column function matches MATCH column data and INFORMATION column
data:

INFORMATION | TYPE | MATCH
Jim Firstname
Jack Firstname Jack
Tom Firstname
Jones Lastname
Smith Lastname Smith
Davis Lastname Davis

I would really appreciate help with this

Thanks - G

Reply to this Message

Otto Moehrbach - 12 May 2006 16:17 GMT

Anything that you enter into any cell in the MATCH column will wipe out
whatever formula or function or other entry that is already there. What you
should do is enter your "matching" data into some other column and use VBA
to look through all your data and put the matching data in the proper cells
of the MATCH column. Post back if you think this would work for you and you
need help with it. HTH Otto

>I have a 3 columns of data:
>
[quoted text clipped - 30 lines]
>
> Thanks - G

Reply to this Message

Gilly - 12 May 2006 16:53 GMT

That makes sense. I don't know anything about VBA. Is it used in a macro?

> Anything that you enter into any cell in the MATCH column will wipe out
> whatever formula or function or other entry that is already there. What
> you should do is enter your "matching" data into some other column and use
> VBA to look through all your data and put the matching data in the proper
> cells of the MATCH column. Post back if you think this would work for you
> and you need help with it. HTH Otto

Reply to this Message

Otto Moehrbach - 13 May 2006 14:15 GMT

VBA is the language that is used in a macro. I will work up something to do
what you want. Otto

> That makes sense. I don't know anything about VBA. Is it used in a macro?
>
[quoted text clipped - 4 lines]
>> proper cells of the MATCH column. Post back if you think this would work
>> for you and you need help with it. HTH Otto

Reply to this Message

Otto Moehrbach - 13 May 2006 14:50 GMT

Gilly
Here is a macro that will do what you want. I assumed the INFORMATION
column is Column A and the data starts in A2. I also assumed the MATCH
column is Column C. And the Match Data (what you want to match) is in
Column E starting in E2. Watch out for line wrapping in these messages.
The macro will not work if the macro lines are wrapped where they are not
supposed to be wrapped. I am also sending you the small file I used to
develop this macro. I am sending that to the email address that is attached
to your post. If this is not a valid email address for you, send me an
email with a valid address for you and I will send you the file. My email
address is ottokmnop@comcast.net. Remove the "nop" from this address. HTH
Otto
Sub InsertMatch()
Dim RngColA As Range
Dim RngMatch As Range
Dim i As Range
Set RngColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set RngMatch = Range("E2", Range("E" & Rows.Count).End(xlUp))
For Each i In RngMatch
If Not RngColA.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
_
RngColA.Find(What:=i.Value, LookAt:=xlWhole).Offset(, 2) =
i.Value
Next i
End Sub

Reply to this Message