?
Solved

Comparing 2 excel worksheets and reporting differences

Posted on 2004-08-26
8
Medium Priority
?
174 Views
Last Modified: 2010-05-02
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
Comment
Question by:irishmanjb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 

Author Comment

by:irishmanjb
ID: 11903463

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
 
LVL 8

Expert Comment

by:plq
ID: 11910413
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
Independent Software Vendors: 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!

 

Author Comment

by:irishmanjb
ID: 11912188
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
 
LVL 8

Expert Comment

by:plq
ID: 11912273
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
 

Author Comment

by:irishmanjb
ID: 11912448
Thanks !
0
 

Author Comment

by:irishmanjb
ID: 11968665
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
 
LVL 8

Accepted Solution

by:
plq earned 2000 total points
ID: 11968768
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month9 days, 7 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question