Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possibly a loaded question, but I think | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Newbie With A Question | Excel Worksheet Functions | |||
Anybody Help with previous question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions |