Macro to compare and copy ranges of cells

V

Vanessa

Hi! I've been trying to figure this out for a while, and I just can't
seem to come up with anything even close to right. I receive a list
every morning and need to compare it to the list I received yesterday,
copying over the additional data I entered on yesterday's list for all
those who are still on today's list. So I have two excel worksheets
("today" and "yesterday") and I need a macro that will compare a range
of cells on "yesterday" to "today" and copy data from the same row in
"yesterday" to the matched column in "today". (The lists are actually
in two separate workbooks, but I was trying to do this after copying
them into different sheets in the same workbook because I thought that
would be easier for me to write the macro.)

Here's an illustration:

Yesterday's list:
Column A Column B Column C Column R Column S Column T
Column U Column V
Last1 First1 DOB1 ………….. R1 S1
T1 U1 V1
Last2 First2 DOB2 ………….. R2 S2
T2 U2 V2
Last3 First3 DOB3 ………….. R3 S3
T3 U3 V3
Last4 First4 DOB4 ………….. R4 S4
T4 U4 V4
Last5 First5 DOB5 ………….. R5 S5
T5 U5 V5
Last6 First6 DOB6 ………….. R6 S6
T6 U6 V6
Last7 First7 DOB7 ………….. R7 S7
T7 U7 V7
Last8 First8 DOB8 ………….. R8 S8
T8 U8 V8

Today's list:
Column A Column B Column C Column R Column S Column T
Column U Column V
LastA FirstA DOBA
LastB FirstB DOBB
Last1 First1 DOB1
LastC FirstC DOBC
LastD FirstD DOBD
Last8 First8 DOB8
LastE FirstE DOBE
LastF FirstF DOBF

Today's list as I want it after macro:
Column A Column B Column C Column R Column S Column T
Column U Column V
LastA FirstA DOBA
LastB FirstB DOBB
Last1 First1 DOB1 ………….. R1 S1
T1 U1 V1
LastC FirstC DOBC
LastD FirstD DOBD
Last8 First8 DOB8 ………….. R8 S8
T8 U8 V8
LastE FirstE DOBE
LastF FirstF DOBF

I have really spent a lot of time working on this, but I don't
understand enough about VBA to make it work. Any solutions or advice
would be MUCH appreciated!!! It will save me so much time every
morning if I don't have to do this manually. Please let me know if I
haven't explained something clearly--I'm happy to try to clarify
further.

Thank you!

Vanessa
 
D

Don Guillett Excel MVP

This can be done from either a workbook or another sheet in the same
workbook using vba FIND within an if loop.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
J

JCO

If possible, I would like to see the results too.
This is something that I've had to do many times. While I may have
different columns to compare, it would be interesting to see.

"Don Guillett Excel MVP" wrote in message

This can be done from either a workbook or another sheet in the same
workbook using vba FIND within an if loop.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
D

Don Guillett Excel MVP

If possible, I would like to see the results too.
This is something that I've had to do many times.  While I may have
different columns to compare, it would be interesting to see.

"Don Guillett Excel MVP"  wrote in message
This can be done from either a workbook or another sheet in the same
workbook using vba FIND within an if loop.

"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."










- Show quoted text -
Click to expand...

Send YOUR file if desired, following the same instructions.
 
D

Don Guillett Excel MVP

Send YOUR file if desired, following the same instructions.- Hide quoted text -

- Show quoted text -
Click to expand...
Sent this for ACTUAL file
detailed for easy understanding

Sub MatchEmUpSAS()
Set ds = Sheets("Today")
Set ss = Sheets("Yesterday")
lr = ss.Cells(Rows.Count, 1).End(xlUp).Row
For Each co In ss.Cells(3,
"f").Resize(lr).SpecialCells(xlCellTypeConstants)
With ds.Columns(1)
Set c = .Find(What:=ss.Cells(co.Row, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False)


If Not c Is Nothing Then
firstAddress = c.Address
Do

If ds.Cells(c.Row, 1) = ss.Cells(co.Row, 1) And _
ds.Cells(c.Row, 2) = ss.Cells(co.Row, 2) And _
ds.Cells(c.Row, 3) = ss.Cells(co.Row, 3) Then
ss.Cells(co.Row, "f").Resize(, 20).Copy ds.Cells(c.Row, "f")
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next co
End Sub
 
Top