I have two excel workbooks that are generated on a daily basis. What
I would like to do is find out if information on todays workbook is a
match with information on the previous days workbook and if it is I
want to copy the matching information from a 3rd column and place it
in todays workbook.

The workbooks have the same format
(old workbook) (new
workbook)
Info to copy ticket number copy here ticket
number
---------------- -------------------
------------- -------------------
19809 5550157 5499795
19798 5624887 5786878
5499695 5550157
5499691 5079389
5499693
19773 5499698

As you can see the length of the ticket number column is different
every day it may have 1 item in it or 1000. As you can see ticket
number 5550157 matches in both workbooks, so I need to copy 19809 into
the new workbook and put them in the row that matches 5550157. Of
course these numbers are not constant, but they are unique to each
days work. So if 5550157 shows up on the previous day and today's
workbook it is the only time it shows up, there are not mulitple
instances of that check number in the report. I would like to use a
macro to run this match with no interaction from the end user. I have
tried to use offset to make dynamic ranges and using match but can't
figure out how to actually copy the data from a different column if
there is actually a match.

I hope that this is clear.

-Chris

Check out these functions:

VLOOKUP - http://www.contextures.com/xlFunctions02.html

INDEX MATCH - http://www.contextures.com/xlFunctions03.html




" wrote:

I have two excel workbooks that are generated on a daily basis. What
I would like to do is find out if information on todays workbook is a
match with information on the previous days workbook and if it is I
want to copy the matching information from a 3rd column and place it
in todays workbook.

The workbooks have the same format
(old workbook) (new
workbook)
Info to copy ticket number copy here ticket
number
---------------- -------------------
------------- -------------------
19809 5550157 5499795
19798 5624887 5786878
5499695 5550157
5499691 5079389
5499693
19773 5499698

As you can see the length of the ticket number column is different
every day it may have 1 item in it or 1000. As you can see ticket
number 5550157 matches in both workbooks, so I need to copy 19809 into
the new workbook and put them in the row that matches 5550157. Of
course these numbers are not constant, but they are unique to each
days work. So if 5550157 shows up on the previous day and today's
workbook it is the only time it shows up, there are not mulitple
instances of that check number in the report. I would like to use a
macro to run this match with no interaction from the end user. I have
tried to use offset to make dynamic ranges and using match but can't
figure out how to actually copy the data from a different column if
there is actually a match.

I hope that this is clear.

-Chris

Here's an example:

Sub test()
For Each cell In Range("A1:A50")
Set found = Range("B1:B50").Find( _
What:=cell.Value, _
LookIn:=xlValues)
If Not found Is Nothing Then
cell.Copy found.Offset(, 1)
End If
Next cell
End Sub

--
Dan


On Apr 17, 12:43*pm, wrote:
I have two excel workbooks that are generated on a daily basis. *What
I would like to do is find out if information on todays workbook is a
match with information on the previous days workbook and if it is I
want to copy the matching information from a 3rd column and place it
in todays workbook.

The workbooks have the same format
* * * * * (old workbook) * * * * * * * * * * * * * * * * * * *(new
workbook)
Info to copy * ticket number * * * * * * * * * * * copy here * ticket
number
---------------- * -------------------
------------- * *-------------------
19809 * 5550157 * * * * * * * * * * * * 5499795
19798 * 5624887 * * * * * * * * * * * * 5786878
* * * * 5499695 * * * * * * * * * * * * 5550157
* * * * 5499691 * * * * * * * * * * * * 5079389
* * * * 5499693
19773 * 5499698

As you can see the length of the ticket number column is different
every day it may have 1 item in it or 1000. *As you can see ticket
number 5550157 matches in both workbooks, so I need to copy 19809 into
the new workbook and put them in the row that matches 5550157. *Of
course these numbers are not constant, but they are unique to each
days work. *So if 5550157 shows up on the previous day and today's
workbook it is the only time it shows up, there are not mulitple
instances of that check number in the report. *I would like to use a
macro to run this match with no interaction from the end user. *I have
tried to use offset to make dynamic ranges and using match but can't
figure out how to actually copy the data from a different column if
there is actually a match.

I hope that this is clear.

-Chris

Thanks to you both, I was able to use both these examples to make a
macro that will find and copy the data I need

Thanks again,
Christopher

On Apr 17, 12:36*pm, "Dan R." wrote:
Here's an example:

Sub test()
* For Each cell In Range("A1:A50")
* * Set found = Range("B1:B50").Find( _
* * * * * * * * * What:=cell.Value, _
* * * * * * * * * LookIn:=xlValues)
* * If Not found Is Nothing Then
* * * cell.Copy found.Offset(, 1)
* * End If
* Next cell
End Sub

--
Dan

On Apr 17, 12:43*pm, wrote:



I have two excel workbooks that are generated on a daily basis. *What
I would like to do is find out if information on todays workbook is a
match with information on the previous days workbook and if it is I
want to copy the matching information from a 3rd column and place it
in todays workbook.

The workbooks have the same format
* * * * * (old workbook) * * * * * * * * * * * * * * * * * * *(new
workbook)
Info to copy * ticket number * * * * * * * * * * * copy here * ticket
number
---------------- * -------------------
------------- * *-------------------
19809 * 5550157 * * * * * * * * * * * * 5499795
19798 * 5624887 * * * * * * * * * * * * 5786878
* * * * 5499695 * * * * * * * * * * * * 5550157
* * * * 5499691 * * * * * * * * * * * * 5079389
* * * * 5499693
19773 * 5499698

As you can see the length of the ticket number column is different
every day it may have 1 item in it or 1000. *As you can see ticket
number 5550157 matches in both workbooks, so I need to copy 19809 into
the new workbook and put them in the row that matches 5550157. *Of
course these numbers are not constant, but they are unique to each
days work. *So if 5550157 shows up on the previous day and today's
workbook it is the only time it shows up, there are not mulitple
instances of that check number in the report. *I would like to use a
macro to run this match with no interaction from the end user. *I have
tried to use offset to make dynamic ranges and using match but can't
figure out how to actually copy the data from a different column if
there is actually a match.

I hope that this is clear.

-Chris- Hide quoted text -

- Show quoted text -