Concatenate Data

Using Excel 2007

I have a sheet tab (sheet1) that has a list of product IDs in column A and relative codes in column B. There can be duplicate Product IDs in sheet1.

10.JPG

How can I set up some formulas and break out the table above to look like the table below?

12.JPG

If this cannot be done, can someone show me how to break out the data like the table below?

13.JPG

Your help is greatly appreciated.
Karen

If you post those sample data in a workbook instead it will be a quick fix.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

I don't see your sheet.
Anyway first select your product ID's and move them and, from data ribbon tab, remove duplicates.

then with this new list, let's say it's in col A of sheet2 with data in sheet1

Must be entered as an array using CNTRL SHFT ENTER instead of ENTER (if done correctly, you'll see {} around your formula) in B2 of sheet 2
=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A2,ROW($A$2:$A$10)-ROW($A$2)+1,1E+100),COLUMN(A1))),"")
copied down and to the right
Does that work for you?

Here it is with the formula (and method) I described above. Questions?

Sorry, wrong choice of words, I meant copy them. Then you can remove duplicates from the copied set.