Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default compare same data of column in two workbooks

Hello,

I have two workbooks. Both of them has 5 columns. 1st column (date),
2nd column (product code), 3rd column (product type) 4th column
(account number) and last 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 delinquency accounts.

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


In the internet, I found one macro quite similar to this one. however,
I need to modify it (matching criteria is for column 4, same account
number in two workbooks) . thanks
===================================
Sub RankOtherWorkbook()
Dim otherBook As Workbook

Dim thisSheet As Worksheet
Dim otherSheet As Worksheet


Dim thisCell As Range
Dim otherCell As Range

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)
Do
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) Then
otherCell.Offset(0, 3).Value = thisCell.Offset(0, 3)

End If
Loop Until IsEmpty(thisCell)
Set otherCell = otherCell.Offset(1, 0)
Loop
End Sub
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare data in two different workbooks Dave Eade Excel Discussion (Misc queries) 1 February 24th 10 12:14 PM
How do I compare data in 2 workbooks Mzansi Excel Programming 5 February 2nd 06 07:30 PM
Compare same Column in 2 Workbooks GregR Excel Programming 2 September 1st 05 06:13 PM
How can I compare data on 2 workbooks Joshua Excel Discussion (Misc queries) 1 June 22nd 05 04:07 PM
compare data from one column with another and compare result to yet another Matt Williamson[_3_] Excel Programming 1 September 25th 03 08:54 PM


All times are GMT +1. The time now is 07:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"