Combining more than one color per inventory number

[roxdrob01]

Is there any type of formula that would allow me to make the below example.1 into example.2 or example.3?

example.1
Inv# Color
ADJ4 White
ADJ4 Blue
ADJ4 Red
ADJ4 Silver
ADJ5 Blue
ADJ5 Black
ADJ6 Silver

example.2
Inv# Color1 Color2 Color3
ADJ4 White Blue Red
ADJ5 Blue Black
ADJ6 Silver

>>> OR <<<

example.3
Inv# Colors
ADJ4 White, Blue, & Red
ADJ5 Blue, & Black
ADJ6 Silver

TIA

[alansidman]

What is the maximum number of colors any one Inv# can have?

[roxdrob01]

Four at most

[roxdrob01]

It would also work with an MS Access crosstab query results (see attached)

[alansidman]

Here is a Power Query Solution. The following is the Mcode and I have attached the file for your review.

Please Login or Register  to view this content.

[roxdrob01]

Thank you so much. That looks impressive but I have no idea what to do with it. Where do I plug in the code?

[alansidman]

Look at my signature and there are two links to learn about Power Query.

[roxdrob01]

Thank you. I've been googling.

[roxdrob01]

This is great but I think maybe I didn't explain what I was looking for enough.

I would want the colors in a phrase like "blue, black & green" or "yellow & beige" or at most "red, white, blue & silver". Does that make more sense? Like taking what you did, then concatenating it. Where my breakdown comes in is that it's not definite how many colors will appear in the phrase...one, two, three or four.

[Bo_Ry]

Please try Power query
for Result

Please Login or Register  to view this content.

for Crosstab

Please Login or Register  to view this content.

[roxdrob01]

This is great! Thanks so much.