I have a work book that contains 2 sheets of data. It contains pricing information for new and used parts. In these sheets the same number for the part might be on both sheets but there are two different prices. I am looking for a way to combine these sheets to contain only unique part numbers, but contain all of the prices.

I have created a sample work book to illustrate what I am trying to accomplish.

Attached Files

Sample Sheets 1.xlsx (13.6 KB, 9 views)

Easy-peasy, bremen22; you don't even need to write a VBA program for it. Oh, if I were going to do this many times I would certainly write a program to do it, but for a one-time chore here's what I'd do:

1) Make a list that comprises all the names on both lists. I'm assuming the actual data is pretty long, too long to do this manually, so probably the easiest way is:

a) On your Goal sheet, first copy all the names from the longer list (in this case the one on Sheet2).

b) On the shorter list, create a temporary column reading =MATCH(A2,Sheet2!A:A,0). The names that appear on both worksheets will have a number; those that appear on Sheet1 and not on Sheet2 will be marked #N/A.

c) Sort this sheet by the MATCH column so that all the ones marked #N/A are clustered together. Those are the ones you want; copy the names in that cluster to the bottom of the Goal sheet, then sort Sheet1 back into its original order (assuming that's important to you).

Sort the Goal sheet by name, if you want, but whether you do or not, the Goal sheet now has a list of all the names for Sheet1 and Sheet2.

2) Now that you have that list transferred to the Goal sheet, use =VLOOKUP to pull the desired data from Sheet1 and Sheet2.