VBA Code for Identifying Every Unique Value Associated with An Adjacent Repeating Value

[doug_38]

Hello:

I have a material list in Excel that has unique part numbers listed vertically (one for every cell in the column) - no duplicates - (list "A"). In another list - seperate tab - (list "B"), I have the same part numbers listed vertically but they are repeating; for every part number listed, there is a unique quantity listed vertically in the next column over - one unique quantity number listed next to every repeated part number. Consequently, I want to identify the part number listed in list "A" and bring into list "A" all of the associated quantites for that particular part number from list "B". The quantities would be listed horizontally next to the unique part number in the column. Please see below:

List "A" (Sheet 1)

Part number
111.2222.333
222.3333.444
555.6666.777

List "B" (Sheet 2)

Part number Quantities
111.2222.333 1,000
111.2222.333 2,000
111.2222.333 3,000
222.3333.444 1,500
222.3333.444 2,100
555.6666.777 25
555.6666.777 150
555.6666.777 200

RESULT
List "A" (Sheet 1) - Result!

Part number Quantities (each in a different cell listed horizontally)
111.2222.333 1,000 2,000 3,000
222.3333.444 1,500 2,100
555.6666.777 25 150 200

The range in each list ("A" and "B") will vary depending upon the length of each downloaded list into Excel. Consequently, the macro needs to be adjustable for values in arrays; I'm assuming.

What is the macro code to do this?

Any help is greatly appreciated.

[Richard Buttrey]

You may not need a macro.

Would a pivot table with the Part number in the Row labels and Qty in the column labels and values section do.

Admittedly it shows null for quantities that don't exist and the table will contain as many columns as you have quantities. You may however find that a pivot table with the Qty shown as a second row label gives you what you want albeit vertically rather than horizontally.

[doug_38]

Richard:

I did think about doing this via pivot table, but I then need to reference the pivot table from list "A" to pull in the various quantites per part number horizontally. How do I accomplish this if the pivot table gives the quantities below the part number vertically? Additonally, I want to do this so that for every change in the part number in list "A", the quantities for the next number listed vertically in list "A" will be brought in horizontally. How can I accomplish this with reference formulas after the pivot table is created? I tried messing with the pivot table attributes so that the part numbers were listed as unique items vertically while the quantities were listed horizontally for each part number to no avail.

Thanks,
Doug