Looking for a macro or function to concatenate every unique combination

Hi everyone,

I am working on a project where I have 12 adjective keywords (N2:Y2) and 4 noun keywords (J2:M2). I need to concatenate every unique(no repeating words) combination of these two groups so that it looks like:

N J
N K
N L
N O J
N O K
N O L
N O M
etc...

If there is a blank in any of the keyword cells for a combination, I would like the corresponding concatenated cell to be blank as well. I would like to put the first concatenated cell in Z2, keeping all concatenations in the same row.

I've attached a test file below. Any help would be greatly appreciated! Thanks!

Try this.

Please Login or Register  to view this content.

This is a great start, thanks! Just a few things of note:

I'm a newbie when it comes to code, so I apologize if these are things that I can easily fix in VBA. I would like to switch the word order so that J1:M1 is always the last word.

I also would like to add a condition where if one of the cells is blank, the cell will come up blank rather than filling in the remaining words (ex. If one of the possible combinations is N1 M1, and M1 is blank, I would like the cell with that combination to be blank as well.

Finally, I'd like to see what I would need to add to the code if I want to continue to do the combinations of 4 words, 5 words, etc.

I really appreciate the help. Again, I'm a newbie, but I was playing around with your code, and I feel like I'm getting an understanding of it, but I'm not quite there yet. Thank you again so much for your help!

Are you also needing the adjectives to be in all possible sequences, ie:
N J
N O J
N O P J
N P J
N P O J
O J
O N J
O N P J
etc?

If so, that list of combinations will get extremely large, especially with 12 adjectives. It increases geometrically.

No, sequence does not matter, thankfully. So it would be more like:

N O P J
N O J
N P J
O P J
N J
O J
P J

Bump. If Nate or anyone else that can help needs more information, I'll do my best to provide. Thanks!

I didn't mean to leave you hanging. After I got a better understanding of what you wanted, I realized it would be a more complicate solution than I had thought. It can be done, but I'm not feeling like I can continue taking my employer's time to get it done, as busy as I am right now. Sorry.

That's totally understandable. Thanks for taking a crack at it, though. I appreciate it!

Anyone else that might be able to help?