compare same data in two workbooks

[john-lau]

Hello

I have two workbooks. Both of them has 5 columns. 1st column (date), 2n
colum
(product code), 3rd column (product type) 4th column (account number) and las
column is remark (enter by staffs

Every week, I receive files from other department and name as e.g. week 1,
wee
2. Week 1 has the data of remark (column 5) Its related to delinquenc
accounts

If there are same accounts number in two workbooks (colum 4) , for example a
compare week 1 excel and week 2 excel, I would like to write the macro to cop
remark (column 5) of same account from week 1 file to week 2 ( for example
i
week 1, there is remark for account 123-456789, not yet pay the debt). If
receive week2 report, the account still is in excel, I can copy the remar
fro
week 1 excel to week 2 excel


In the internet, I found one macro quite similar to this one. however, I nee
t
modify it . thank
==================================
Sub RankOtherWorkbook(
Dim otherBook As Workboo

Dim thisSheet As Workshee
Dim otherSheet As Workshee


Dim thisCell As Rang
Dim otherCell As Rang

Set otherBook = Workbooks("Book2.xls"
Set thisSheet = ThisWorkbook.Sheets("Sheet1"
Set otherSheet = otherBook.Sheets("Sheet1"

Set otherCell = otherSheet.Cells(2, 1
Do While Not IsEmpty(otherCell
otherCell.Offset(0, 3).Value = "#NA
Set thisCell = thisSheet.Cells(1, 1
D
Set thisCell = thisCell.Offset(1, 0
If (thisCell.Value = otherCell.Value And
thisCell.Offset(0, 1).Value <= otherCell.Offset(0, 1).Value And
thisCell.Offset(0, 2).Value >= otherCell.Offset(0, 2).Value) The
otherCell.Offset(0, 3).Value = thisCell.Offset(0, 3

End I
Loop Until IsEmpty(thisCell
Set otherCell = otherCell.Offset(1, 0
Loo
End Sub

 

[GS]

Check your previous post for a solution.

Also, try staying with the original thread instead of creating new ones
for the same topic! Makes it hard to follow when there's multiple
posting of the same question/request.

 

[john-lau]

john-lau wrote on 04/12/2011 04:41 ET

Hello

I have two workbooks. Both of them has 5 columns. 1st column (date), 2n colum
(product code), 3rd column (product type) 4th column (account number) an las
column is remark (enter by staffs

Every week, I receive files from other department and name as e.g. week 1
week 2. Week 1 has the data of remark (column 5) Its related to delinquenc
accounts

If there are same accounts number in two workbooks (colum 4) , for example a
compare week 1 excel and week 2 excel, I would like to write the macro t cop
remark (column 5) of same account from week 1 file to week 2 ( for example i
week 1, there is remark for account 123-456789, not yet pay the debt). If
receive week2 report, the account still is in excel, I can copy the remar fro
week 1 excel to week 2 excel


In the internet, I found one macro quite similar to this one. however, I nee
to modify it . thank
==================================
Sub RankOtherWorkbook(
Dim otherBook As Workboo

Dim thisSheet As Workshee
Dim otherSheet As Workshee


Dim thisCell As Rang
Dim otherCell As Rang

Set otherBook = Workbooks("Book2.xls"
Set thisSheet = ThisWorkbook.Sheets("Sheet1"
Set otherSheet = otherBook.Sheets("Sheet1"

Set otherCell = otherSheet.Cells(2, 1
Do While Not IsEmpty(otherCell
otherCell.Offset(0, 3).Value = "#NA
Set thisCell = thisSheet.Cells(1, 1
D
Set thisCell = thisCell.Offset(1, 0
If (thisCell.Value = otherCell.Value And
thisCell.Offset(0, 1).Value <= otherCell.Offset(0, 1).Value And
thisCell.Offset(0, 2).Value >= otherCell.Offset(0, 2).Value) The
otherCell.Offset(0, 3).Value = thisCell.Offset(0, 3

End I
Loop Until IsEmpty(thisCell
Set otherCell = otherCell.Offset(1, 0
Loo
End Su

Dear Gary

Thanks a lot. I wonder if I can ask you one more thing

For example, the matching column changed from 4 to 1, should I modify th
statement: Set rngTarget
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A:$A"
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A"

At this time, I would like to copy column 3 data (no adjacent to column 1
fro
old week 1 excel to new week 2 excel (also column 3). After that , I woul
lik
to cell fill in yellow colour. What should I add macro statement? I trie
several times, please teach me. Thank


Would you mind tell interpet / explain the macro for this type
For lRow = 1 To lLastRo
Set rng =
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole
If Not rng Is Nothing Then
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1
Next

 

[GS]

Dear Gary,

Thanks a lot. I wonder if I can ask you one more thing.

You're welcome. Glad you were able to get it sorted out about replies
to one thread per topic.

For example, the matching column changed from 4 to 1, should I modify the
statement: Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A:$A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")

I have revised the sub (see below) to work with data in ColA and
comments in ColC.

At this time, I would like to copy column 3 data (no adjacent to column 1)
from
old week 1 excel to new week 2 excel (also column 3). After that , I would
like
to cell fill in yellow colour. What should I add macro statement? I tried
several times, please teach me. Thanks


Would you mind tell interpet / explain the macro for this type:
For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then _
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1)
Next

The above code is what's known as a 'Loop' structure that uses a
counter for each iteration. In this case, it's checking in rngTarget
for a match to each cell in rngSource starting in row1 and ending in
the last row containing data in that column. If it finds a match then
it copies the comment in rngSource to the appropriate cell in
rngTarget.


<Revised code>
Sub CompareData()
Dim rngSource As Range, rngTarget As Range, rng As Range
Dim lLastRow As Long, lRow As Long

Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A:$A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Row

For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then
With rngSource.Cells(lRow).Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
rng.Offset(, 2).Interior.ColorIndex = 6
End If
Next
End Sub

***
The above will 'flag' the comment cells yellow. If you only want to
flag the matched cells then replace the If..Then construct with the
following:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Offset(, 2).Value = rng.Offset(, 2)
.Interior.ColorIndex = 6
End With
rng.Interior.ColorIndex = 6
End If

***
If you want to 'flag' both cells on each sheet:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Interior.ColorIndex = 6
With .Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
End With
With rng
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End With
End If

 

[john-lau]

GS wrote on 04/12/2011 15:47 ET

You're welcome. Glad you were able to get it sorted out about replie
to one thread per topic

I have revised the sub (see below) to work with data in ColA an
comments in ColC

The above code is what's known as a 'Loop' structure that uses
counter for each iteration. In this case, it's checking in rngTarge
for a match to each cell in rngSource starting in row1 and ending i
the last row containing data in that column. If it finds a match the
it copies the comment in rngSource to the appropriate cell i
rngTarget


<Revised code
Sub CompareData(
Dim rngSource As Range, rngTarget As Range, rng As Rang
Dim lLastRow As Long, lRow As Lon

Set rngTarget
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A:$A"
Set rngSource
ThisWorkbook.Sheets("Sheet1").Range("$A:$A"
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Ro

For lRow = 1 To lLastRo
Set rng =
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole
If Not rng Is Nothing The
With rngSource.Cells(lRow).Offset(, 2
.Value = rng.Offset(, 2): .Interior.ColorIndex =
End Wit
rng.Offset(, 2).Interior.ColorIndex =
End I
Nex
End Su

**
The above will 'flag' the comment cells yellow. If you only want t
flag the matched cells then replace the If..Then construct with th
following

If Not rng Is Nothing The
With rngSource.Cells(lRow
.Offset(, 2).Value = rng.Offset(, 2
.Interior.ColorIndex =
End Wit
rng.Interior.ColorIndex =
End I

**
If you want to 'flag' both cells on each sheet

If Not rng Is Nothing The
With rngSource.Cells(lRow
.Interior.ColorIndex =
With .Offset(, 2
.Value = rng.Offset(, 2): .Interior.ColorIndex =
End Wit
End Wit
With rn
.Interior.ColorIndex =
.Offset(, 2).Interior.ColorIndex =
End Wit
End I

Garr

Free usenet access at http://www.eternal-september.or
ClassicVB Users Regroup! comp.lang.basic.visual.mis

Thanks a lot, I tried macro several times. Column 3 in two workbook (week
an
week2) excel are highlighted. However, data of column 3 in week 1 excel i
deleted and no data copied on week 2 excel column 3. It may be som
macrostatments is missing

Secondly, how can I modified the macro statement, therefore, it can applied o
specific sheet
For example, in week 1 excel, there are 2 sheets (west canada and eas
canada)
Week 2 excel also have 2 sheet
As I would like to compare week 1 excel sheet 2 with week 2 excel sheet
(bot
are column 3). Which macro statement should I change

I can attach two excel file to let you have a look. How can I attach fil
here
Thanks.

 

[GS]

john-lau pretended :

Thanks a lot, I tried macro several times. Column 3 in two workbook (week1
and
week2) excel are highlighted. However, data of column 3 in week 1 excel is
deleted and no data copied on week 2 excel column 3. It may be some
macrostatments is missing.

There are no statements to delete any values unless the values in
rngSource-Col3 are empty, in which case this will overwrite any
existing values in rngTarget-Col3.

Secondly, how can I modified the macro statement, therefore, it can applied
on
specific sheet?
For example, in week 1 excel, there are 2 sheets (west canada and east
canada).
Week 2 excel also have 2 sheet.
As I would like to compare week 1 excel sheet 2 with week 2 excel sheet 2
(both
are column 3). Which macro statement should I change?

Just edit the workbook names AND the respective worksheet names. If
you're saying the 2 files will already have specific sheet names then
I'd not put this code in either file; I'd put it in a separate file
saved as XLA and installed as an Addin. That way the code can be used
with any workbooks as needed.

Another change I'd make is to pass the workbook names and sheet names
and the source/target ranges as arguments to the procedure. This
precludes using a setup procedure to prompt the user for this info.
Alternatively, it can all be done in a single procedure if that's what
you prefer.

Let me know which way you want to go so I can post revised code.

I can attach two excel file to let you have a look. How can I attach file
here?
Thanks.

It depends how you're accessing this forum. I use a newsreader which
allows attaching files same as an email app. Look for a feature in the
app you're using to see if that's supported.

OR you an email me here:

gesansomATnetscapeDOTnet

by making the obvious revisions of the uppercase to the appropriate
characters.