Formula to associate all values in column A to every value in column B

[CB5]

I have 2 columns and I'd like to take every single value in column A and associate it to every value in column B. The goal is to have column A have every association to column B. My spreadsheet is huge so I can't really do this by just manually manipulating the data.

I've attached a pretty basic example of what I'm looking for as I'm having a hard time describing it through text. A1 - B7 has the raw data and A14 - B38 is what I'm hoping to do with a formula.

Thank you,
CB5

[Paul]

Hi CB5, perhaps something like this will work for you. This puts the result set into columns D:E.

Please Login or Register  to view this content.

[CB5]

Hi Paul,

Thank you for the quick response. I'm pretty green when it comes to anything outside of standard excel formulas...where would I implement your solution?

[FlameRetired]

While waiting for Paul here is a formula solution.

In the attached find two helper cells ... D13:E13. D13 is Named in Name Manager Name_Count Likewise for E13 Color_Count

Then in D15 filled down until you get blanks

Please Login or Register  to view this content.

and in E15 filled down until you get blanks.

Please Login or Register  to view this content.

[Paul]

Open your workbook then press ALT+F11 to open the Visual Basic editor.
Double-click on 'Sheet1 (Sheet1)' on the left, then paste the code I provided on the right.
Click anywhere within the code, then press F5 to run that macro.
Go look at your worksheet.

The code assumes your data is in A2:A## and B2:B##, then puts the results into D1:D## and E1:E##.

You'll have to save the file as a Macro-enabled (.xlsm) file to keep the code available. If you save as .xlsx, it will prompt you about the code/macro.

[CB5]

Dave,

Thank you very much! The one part I'm not sure I understand is Output 2's reference to Output . For my actual data I'm hoping to create I don't have Output (A15:B15), rather that is what I'm trying to produce from My 2 raw data columns A1:B7

I ended up just attaching my actual raw data. If you have time could you see if the formulas could be updated for the raw data listed here?

Thank you

[FlameRetired]

Thank you very much! The one part I'm not sure I understand is Output 2's reference to Output . For my actual data I'm hoping to create I don't have Output (A15:B15), rather that is what I'm trying to produce from My 2 raw data columns A1:B7

Yes that is what Output2 does. I did it in columns D:E to make comparing the returns with the intended (A:B) easier.

Here is another attachment with all work done in columns A:B. The named helper cells remain the same.

PS I haven't looked at your latest upload. Will do now.

[FlameRetired]

Just looked at your latest upload (Post #6). The INDEX range in D15 needs to be changed. Try this formula. It works at my end.

Please Login or Register  to view this content.

[CB5]

This works perfect now! Thank you!

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback.