CC268
01-16-2017, 03:30 PM
This is a bit of a tricky problem so I am hoping someone is willing to figure this out with me (it will probably be easy to you VBA experts).
I have two separate sheets (the data is actually in two separate workbooks, but for the sake of this we will just say they are two separate sheets) - we will call them Sheet1 and Sheet2. They both contain a list of Next Higher Assembly (NHA) Part Numbers and Part Numbers, among many other columns of data. See attached sample workbook with sample Sheet1 and Sheet2. This should be somewhat helpful to give you a visual.
Sheet1 contains about 2200 lines of data, while Sheet 2 contains about 4000 lines of data. ALL Next Higher Assembly (NHA) Part Numbers and Part Numbers listed in Sheet1 will be in Sheet2, but there are Next Higher Assembly (NHA) Part Numbers and Part Numbers in Sheet2 that aren't in Sheet1 (hence the difference in how many lines of data there are)
What I want to do is write a VBA code that will:
1. Search in Sheet2 for BOTH the NHA Part Number AND Part Number listed in each row of Sheet1 (two criteria VLOOKUP maybe?)
2. If BOTH the NHA Part Number and Part Number from Sheet1 match what is listed in Sheet2, bring over the corresponding data from Sheet 2 (columns A:AN) - Yes there will be some redundant data columns such as NHA Part Number, Part Number, etc - I can write the VBA to simply delete those redundant columns once I am done.
3. Here is the tough part: there will obviously be part numbers that won't be brought over to Sheet1 from Sheet2 since Sheet1 does NOT contain all part numbers. I need a way to still bring over all the data from Sheet2 even if it is not found with the VLOOKUP (or whatever you use). Maybe once the VLOOKUP is done you can use a function to compare the final Sheet1 list to the Sheet 2 list. Whatever is not brought over can be marked, highlighted, etc. I can then manually bring over the data. Maybe you can simply mark something in the last column if the row in Sheet 2 is not in Sheet 1? If you guys have a better way of doing it by all means go for it.
Does this make sense?
I have two separate sheets (the data is actually in two separate workbooks, but for the sake of this we will just say they are two separate sheets) - we will call them Sheet1 and Sheet2. They both contain a list of Next Higher Assembly (NHA) Part Numbers and Part Numbers, among many other columns of data. See attached sample workbook with sample Sheet1 and Sheet2. This should be somewhat helpful to give you a visual.
Sheet1 contains about 2200 lines of data, while Sheet 2 contains about 4000 lines of data. ALL Next Higher Assembly (NHA) Part Numbers and Part Numbers listed in Sheet1 will be in Sheet2, but there are Next Higher Assembly (NHA) Part Numbers and Part Numbers in Sheet2 that aren't in Sheet1 (hence the difference in how many lines of data there are)
What I want to do is write a VBA code that will:
1. Search in Sheet2 for BOTH the NHA Part Number AND Part Number listed in each row of Sheet1 (two criteria VLOOKUP maybe?)
2. If BOTH the NHA Part Number and Part Number from Sheet1 match what is listed in Sheet2, bring over the corresponding data from Sheet 2 (columns A:AN) - Yes there will be some redundant data columns such as NHA Part Number, Part Number, etc - I can write the VBA to simply delete those redundant columns once I am done.
3. Here is the tough part: there will obviously be part numbers that won't be brought over to Sheet1 from Sheet2 since Sheet1 does NOT contain all part numbers. I need a way to still bring over all the data from Sheet2 even if it is not found with the VLOOKUP (or whatever you use). Maybe once the VLOOKUP is done you can use a function to compare the final Sheet1 list to the Sheet 2 list. Whatever is not brought over can be marked, highlighted, etc. I can then manually bring over the data. Maybe you can simply mark something in the last column if the row in Sheet 2 is not in Sheet 1? If you guys have a better way of doing it by all means go for it.
Does this make sense?