How to "assign" a cell to other values in a column?

[jj93]

Hi everyone, apologies if the title is unclear. I'm not really sure what the right terminology is for what I'm trying to accomplish.

Let's say I have multiple values in a column, like some colors:

Red
Blue
Green

And some objects:

Car
House
Mailbox

Basically, I'm looking for a solution that will give me the combination of each of these in two separate columns, like this:

2018-01-04_10-53-32.png

Thanks everyone!

[sandy666]

colors and objects in the same column?
any pattern how many colors and how many objects?
colors and objects could be in two columns?
are you allowed to use PowerQuery?

[jj93]

Hi sandy,

Preferable to keep them in two separate columns.
Not sure what you mean by pattern, but they don't need to be in any specific order and I would need to use the solution for varying list sizes.
Yes, two columns.
Yes, I have PowerQuery but haven't used it before.

Thank you for the reply!

[MrShorty]

It looks like you are simply generating combinations.
1 -- 1 -- color(1) -- item(1)
1 -- 2 -- color(1) -- item(2)
and so on.

I have a post here with a spreadsheet that can do this: https://www.excelforum.com/tips-and-...thout-vba.html My goal was to not use VBA/macros, though the post also includes a basic nested For..Next loop that can also generate combinations like this. I don't know if you are worried about whether to use VBA or not, but I think that should help with the basic process of generating combinations.

[sandy666]

With PowerQuery

Here is a code (first option):

Please Login or Register  to view this content.

or second option:

Please Login or Register  to view this content.

EDIT:
I forgot you don't know PQ, so here is:
Getting Started with Get & Transform (truly PowerQuery) in Excel 2016
Microsoft Power Query for Excel

and here is a file