[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Comparing 2 excel worksheets and reporting differences

I have a project where I need to compare the contents of 2 lists.  I am hoping to set this up with a macro in a master sheet.
I need to compare List 1 with about 200 0ther lists.

LIST 1
site      Description-Ver g      Price
1      UP-33xx_060104__      0
45      COUPONS_________      0
46      ****************      0
50      OPEN DD-COUPON__      5000
53      $ FOR 1 ECLAIR__      0
101      6 FREE W-6 DON__      299
102      6 FOR $ WITH 6__      329
104      6 FREE DON W-50M      329

LIST2
site      Description-Ver I      Price
            
1      UP-33xx_060104__      0
2      PC#338153i______      0
45      COUPONS_________      0
46      ****************      0
50      OPEN DD-COUPON__      5000
53      $ FOR 1 ECLAIR__      0
101      6 FREE W-6 DON__      299
102      6 FOR $ WITH 6__      329
104      6 FREE DON W-50M      329


On a seperate worksheet
This list is actually up to 3000 lines long.
I need to know items in list 2 that are not in list 1
Price discrepencies between list one and list 2

Thanks



0
irishmanjb
Asked:
irishmanjb
  • 4
  • 3
1 Solution
 
irishmanjbAuthor Commented:

I did see this but need help putting this to use.
I tried insert module and then run it as a macro.  I cannot get it to work.

Thanks
 
0
 
plqCommented:
Take this function

Public Function ReadExcelFile(sName As String, Optional lMaxRows As Long = 0) As String
   
    Dim sBuffer As String
    Dim obj As Object
    Dim objdoc As Object
    Dim objSheet As Object
    Dim objCell As Object
    Dim lCount As Long
   
    Set obj = CreateObject("Excel.Application")
    Set objdoc = obj.Workbooks.Open(sName)
    lCount = 0
    For Each objSheet In objdoc.Worksheets
        For Each objCell In objSheet.UsedRange.Cells
            lCount = lCount + 1
            If lCount > lMaxRows And lMaxRows <> 0 Then
                Exit For
            End If
            If objCell.Column = 1 And objCell.Row > 1 Then
                sBuffer = sBuffer & vbCrLf
            End If
            sBuffer = sBuffer & objCell.Value & ","
        Next
        If lCount > lMaxRows And lMaxRows <> 0 Then
            Exit For
        End If
    Next
   
    ReadExcelFile = sBuffer
    objdoc.Close False
    Set objdoc = Nothing
    Set obj = Nothing
   
End Function


And change it to open and compare the two excel files simulateously. Use objSheet.cells(Y,X) to loop through each row and column

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
irishmanjbAuthor Commented:
Plg
Thanks for your post

I am a novice in vbs and have only written very basic scripts.  If you could help me write this as you describe I would appreciate it.  Ideally I want to compare 3 rows in worksheet 1 to 3 rows in worksheet 2.

Sheet 1
item number /  description / price
 
Sheet 2
item number/  descirption / price

I need to report everything in sheet 2 that is not in sheet 1, and any price variances.

Thanks
0
 
plqCommented:
I would like to help more but time pressures at work make it impossible. There are people on ee who would help with this - lets hope they come to this thread !!
0
 
irishmanjbAuthor Commented:
Thanks !
0
 
irishmanjbAuthor Commented:
Can anyone help with this problem?

Sheet 1
item number /  description / price
 
Sheet 2
item number/  descirption / price

I need item numbers on sheet 2 that are not on sheet 1
I need item n umbers that match but have different descriptions
I need to identify item numbers that have a price variance from sheet 1 to sheet 2.

Thanks
0
 
plqCommented:
What about moving the sheets into MS Access and then writing queries to compare ? That gets you out of doing any programming apart from sql/queries.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now