Solved

Comparing 2 excel worksheets and reporting differences

Posted on 2004-08-26
8
167 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now