Update old list data, with new items extracted from a different list?

[J741]

Hi all.

I am relativly inexperienced when it comes to VBA macros in Excel, and am in need of help with a task I am trying to automate in Excel 2007, as follows:

I have 2 spreadsheets with data. One is a static history of all-time, the other is fresh data from a recent snapshot in time. I need to look at a named column in the fresh data and determine if there is any new data values (text) which do not already exist in the historical data. If there is new text data, I need to add it to the historical data list.

Is this something you (the internent community) can help me with? I think it needs the use of arrays in Excel, which is something I am not familiar with handling properly.

Thank you all for your time.

My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[SkipVought]

hi,

If you have a NEW snapshot, how would there be any duplicates?

Duplicates not withstanding, just append the snapshot to the history and then use the remove duplicates feature in the Data tab.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[J741]

O.K. I guess I didn't make myself clear.
I know how to do this manually, but I don't know how to do this programatically in a macro.
I think I need to make use of direct object references and array variables, but I'm not sure how.



Part 1 of the problem (Which I don't have any idea how to do):
---------------------------------------------------------------

If I have a list of words (think like a 'word of the day') in worksheet "History" in C3:C42, and I have a list of many words (with many duplicates) to add to that in worksheet "ThisMonth" in H3:H47342, what macro code can I use to load all of the words located in History!C3:42 into an array variable, and then look through ThisMonth!H3:H47342 and identify only new words that do not already exist in the array and add it to the array, before dumping the array back out to History!C3:C(new number) ?



Part 2 of the problem (for which my ideas did not work):
--------------------------------------------------------

To complicate this even further, I need to figure out how to do this data range referencing indirectly, when the name of the worksheet containing the data (list of words), the column letter, the starting row number, and the ending row number all exist as values in separate cells in a worksheet named 'References' (because they can and do change). Example:

   References!A4="ThisMonth"
   References!A5=3
   References!A6=47342
   References!A7=H

so the new data resides in

Indirect(References!A4 & "!" & References!A7 & References!A5 & ":" & References!A7 & References!A6)

But in a macro using

Range(Indirect(References!A4 & "!" & References!A7 & References!A5 & ":" & References!A7 & References!A6).select

or using

Range(References!A4 & "!" & References!A7 & References!A5 & ":" & References!A7 & References!A6).select

does not seem to work.


If anyone can help me understand how to do this, programatically in a macro, it would be appreciated.


My memory is not as good as it should be, and neither is my memory.

I have forgotten more than I can remember

 

[SkipVought]

I know how to do this manually...

General approch:
Then turn on your macro recorder and record doing it manually. Then either modify the code as needed, or post your recorded code for help customizing.

Your Part 1:

You already have been given TWO suggestions that can help with this issue: ONE given in my FIRST response and the OTHER given just above.

Your Part 2:

Please explain WHAT you need this References sheet for, rather than simply posting code to figure out HOW to use what you think you need to do. It seems rather convoluted!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

try something like this where Sheet 1 column A contains the Historical data, and Sheet2, column E contains the new data:

Sub a()
Sheets("sheet1").Select
' determines the number of records (if you have a title row or other data, subtract)
num1 = Application.WorksheetFunction.CountA(Range("A:A"))
xstart = 2
' determines the number of records (if you have a title row or other data, subtract)
num2 = Application.WorksheetFunction.CountA(Range("sheet2!e:e"))
ystart = 2
' to build our array where the historical array is located
b1$ = "a" & xstart
e1$ = "a" & (xstart + num1 - 1)
all$ = b1$ + ":" + e1$

For y = ystart To num2 + ystart - 1
    m = Application.WorksheetFunction.CountIf(Range(all$), Range("sheet2!e" & y))
    If m = 0 Then
        Range("a" & (xstart + num1)) = Range("sheet2!e" & y)
        num1 = num1 + 1
    End If
Next y
End Sub