Another "combine list" question
 

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

Another "combine list" question
 

Depending on how the data is set up, the way I would do it is create an array
and load it with enough info to determine whether it had a match or not from
sheet B. for example if it were item numbers and prices I would load all item
numbers and prices into the array, then check each item in A for a duplicate,
if it did not exist, add it. Should be fairly simple but more data is needed
for a better answer. The worksheet function way would be to copy and paste
from B to A, check necesarry data for duplication, and manually delete the
rest, this would require brief use of a helper column.
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"SK" wrote:

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

Another "combine list" question
 

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

Another "combine list" question
 

This should help get you started, it checks column A in sheet 2 against
column A in sheet 1 and posts uniques at the bottom

Sub main()

Dim myArray(2000, 1) As String
Dim myIndex As Long
Dim myRow As Long
Dim myExists As Boolean

myIndex = 1

For i = 1 To Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
myArray(myIndex, 1) = Sheets("sheet2").Cells(i, 1)
myIndex = myIndex + 1
Next

myIndex = 1
myRow = 1
myexsists = False

Do Until myArray(myIndex, 1) = ""
myRow = 1
Do Until Cells(myRow, 1) = ""

If myArray(myIndex, 1) = Cells(myRow, 1) Then myExists = True
myRow = myRow + 1
Loop
If myExists = False Then Cells(myRow, 1) = myArray(myIndex, 1)
myExists = False
myIndex = myIndex + 1

Loop
End Sub

--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"SK" wrote:

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

Another "combine list" question
 

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

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

:-D

SK