Another "combine list" question

[SK]

I have scoured all the other "combine list" threads and none are quite
like this problem. I hope someone out there can help

THE SETUP:
*I have two single column lists of product codes, in separate files:
list A in file A, list B in file B. Both are excel 2003.

* List A has about 60% of all the codes i need, list B has a different
~60% of the codes i need

* between them, 100% of the information exists, but there is overlap.

* no editing of file B is allowed (its not mine to play with)


THE AIM
* I want to make list A in file A the master list, with 100% of the
info with no duplication, as neatly as possible, adding the codes that
B has that A doesn't to the bottom of list A

* List A is auto filtered, I'd like to keep it that way

* I'd prefer not to make additional columns and/or worksheets if
possible, but if this is the only way thats ok. However I'd prefer a
vba solution so i can just hit a button on the worksheet in file A to
update it.


MY BACKGROUND
*still very much learning VBA, with about 1 months practical experience

I've read parts of this newsgroup before and found it very useful, this
is the first time i've asked for help from it.

Cheers,
SK

 

[SK]

Hi John
I think I understand the method you are getting at for the first
suggestion. I am assuming you mean loading up an array that exists in
the vba code only, not in any spreadsheets. For me this is preferable
to the second option you propose.

Pseudocode being something like:

Load data from B into an array that only exists here in the code
For each item of data in B
IF it has a match somewhere in A in the spreadsheet
Do nothing
ELSE
Find the bottom of list A
Add the "B" item to next cell below

Am I on the right track?

The next part of the problem is then:
Practically, what code should I be using to read data from file B? I'll
be trawling the help files, but if someone posts quicker than i can
figure it out, all the better ;-)

I don't know what other info is useful to you. I use the list of codes
to look at all manner of wonderful things from other databases dotted
around, and they populate the other columns in the same worksheet, but
they all rely on column A providing the initial code to lookup
everything else. as soon as a new item appears in A, the rest of the
worksheet kicks in fine (i've tested it manually)

Already appreciating the support
SK

 

[SK]

found a webpage with good stuff on reading from closed workbooks:
http://www.exceltip.com/st/Fill_a_L...orkbook_using_VBA_in_Microsoft_Excel/410.html

with the code you've just provided me, I now have a solution!
Many, many thanks!

:-D

SK