Solved

Comparing 2 excel worksheets and reporting differences

Posted on 2004-08-26
8
173 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

687 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