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

 

I receive the new data as an CSV file. I've never worked with Dictionary Data before. So, this will be totally new to me. In your example below:

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

How do I set "yearvalue" and "weekvalue"?

What happens if the search does not find any thing? How do I check for that? Lastly, how do I add that new row into my Master Spreadsheet? Do I have to update this Dictionary Data thing to reflect that?

Can these macros be stored anywhere?

I see that there is a FUNCTION used. Is that new MACRO? I've never used a FUNCTION before as well. Is that coded inside a MACRO?

The DIM statements do not have DATA TYPES associated with the variable names. Which DATA TYPES do I use for the variables? The variable LOAD_DICT_DATA is not in any DIM statements. So, is this a special variable that does not have to be defined by a DIM?

What does the OPTION EXPLICIT do?

--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> 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@...>
> 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 (3)

Recent Activity:

• New Members 3

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

.

__,_._,___