Re: [ExcelVBA] Need help on a Macro to compare two spreadsheets

 

So, you're wanting to look to see if the year and week number are previously
recorded?

How do you "receive" the new data?
Is it in the form of a spreadsheet?

Here's how I would approach it:
Use a "Dictionary Object" to store the row numbers of all of the records in your
"master file".
The Dictionary object is nice, because it is basically a two-dimensional array
that has an index.
So it's VERY fast.
I'd use the (Year & "_" & Week) string as the "index" and store the row number
of the master data

Then, once you figure out how you're going to receive the data,
format it in the same (Year & "_" & Week) format and check to see if it exists
in the dictionary.
If it does, then retrieve the Row number from the dictionary, and update that
row in the master data.
If not, then determine the last row in the master file and add the data (don't
forget to update the dictionary)

Here's a macro for reading/storing the Dictionary Object:

Option Explicit
Public Dict_Data
Sub Auto_Open()
    Dim stat, nRow
    stat = Load_Dict_Data
    '  Read new data,
    '  Check if it exists in Dict_Data
    ' Update row if it exists,
    ' add row if it does not.
End Sub

Function Load_Dict_Data()
    Dim nRows, R, wRecord, stat
       Set Dict_Data = CreateObject("Scripting.Dictionary")
    stat = Dict_Data.RemoveAll
    nRows =
Application.WorksheetFunction.CountA(Sheets("Master_Data").Range("A1:A1000000"))
    For R = 2 To nRows
        wRecord = Sheets("Master_Data").Cells(R, "A").Value & "_" &
Sheets("Master_Data").Cells(R, "B").Value
        If (Not Dict_Data.exists(wRecord)) Then
                Dict_Data.Add wRecord, R
        End If
    Next R
    Load_Dict_Data = Dict_Data.Count
End Function

----------------------------------------------------------

To retrieve the row number of existing data, use:
wRecord = (yearvalue & "_" & weekvalue)
rNum = Dict_Data.item(wRecord)

let me know if you need more assistance.

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: danielrose02 <dan.rose@parker.com>
To: ExcelVBA@yahoogroups.com
Sent: Tue, July 10, 2012 1:04:09 PM
Subject: [ExcelVBA] Need help on a Macro to compare two spreadsheets

 
I need help or ideas on how to create a macro that will hunt for two cells in
one spreadsheet/workbook that equal two cells in another spreadsheet/workbook.

Every week, I will get statistics about usages and it might have 1 or 2 rows of
data:

Row\Column:

1 Year WeekNumber Data
2 2012 27 2.5
3 2012 28 6.5

I want to hunt for this data inside my master spreadsheet and if not present
create(add) two new rows or if found, then update these two rows with the data
from the spreadsheet above. Here is my master spreadsheet/worksheet:

Row\Column

1 Year WeekNumber Data
2 2011 52 6.5
. ..... .. ..
80 2012 26 3.5

How do I start this and what would be the most efficient method to do this.

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

• New Members 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___