Solved

Comparing 2 excel worksheets and reporting differences

Posted on 2004-08-26
8
170 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
  • 4
  • 3
8 Comments
 
LVL 2

Expert Comment

by:newwares
ID: 11902814
0
 

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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