Trying to match product colors based on order numbers

[kurto]

I have a customer order sheet with order numbers and color selection. All products are the same other than color selection. Customers with multiple orders are displayed 1 per line. So there will be 3 orders for example with the same order number and three different colors displayed on 3 rows.

I need to put all 3 colors in 1 field for each order. The idea is to make shipping easier by only having to reference 1 line to see what colors were ordered. Here's an example of what I have:

Order Number Color Name
100107545 black mark
100107545 light grey mark
100107548 black gary
100107551 white rich
100107559 light grey rich
100107559 beige paul
100107560 black paul
100107560 coffee Paul

This is what I need:

Order Number Color Name Results
100107545 black mark black, light grey
100107545 light grey mark
100107548 black gary black
100107551 white rich white, light grey
100107559 light grey rich
100107559 beige paul beige, black, coffee
100107560 black paul
100107560 coffee Paul

I'm hoping there is just a simple formula I can copy down the sheet. Don't understand macros so not a big fan there.

Thanks!

[ELeGault]

No to VBA? would be easier...but if you are strict on macro free workbooks you will need to spend time making 2 helper columns.

If your list exists in A and B, insert 2 columns, so now your lists live in C and D.

Inside A you will say (Assuming C is your order numbers)

A2 =

Please Login or Register  to view this content.

That will give you a Unique order number to lookup

Now in B again assuming C is your Orders
B2=

Please Login or Register  to view this content.

This will give you the order number with its count also for lookup but this time used for looking up your colors which is being assumed in D by now...


Now either off to the right or on a separate sheet (Lets keep it to this sheet for now) you will build a list of orders.

Let's say in F you put a list of numbers from 1 - 100
OrderID|Order Number | Colors
1
2
3
4
5
6
7
8
9
etc...

The numbers are static so write them in down to as many as you think you need. for the Order number we will perform a VLookup From A to C (3 columns) to pull the unique Order Number
G2=

Please Login or Register  to view this content.

Now you have a list of Order Numbers without duplicates.

Next let's pull in the colors, you said a max of 3, just keep in mind you can add as many as you think is necessary...just add another nested IF in the following
H2=

Please Login or Register  to view this content.

So what I am doing here is looking up the Order you just pulled into G combined with a -1,-2,-3 for the first, second, third instance (Again add another if you believe you will need them!)
Take note of the &"," before the second and third Vlookup in the formula. This will add a comma IF there is another color to add to the order, that way you are not left with excessive commas...