Moving cell content from one column to rows based on an adjacent cell

[mjs-111]

Hello, I have a spreadsheet with two columns. Column 1 has text that is repeated as well as some terms that are not repeated. Column has text based on the text in column 1.

I need a formula that will move the text in a col2 cell to a new cell based on the text in the adjacent cell in col1, resulting in the col2 content being converted to rows.

Attached file shows in yellow highlight my current columns, and the blue highlight shows how I want the information to appear.

Anyone?

[#2]

@mjs-111 I have made a solution with some helper columns, attached.  I have used the Office 365 FILTER and UNIQUE functions to populate the first two column of the transformed version, but you could paste-values and use Remove Duplicates to do the same if you don't have those.

[#3]

@Savia  Thank you so much. I haven't implemented it into my "real" spreadsheet yet, but this looks like it will work perfectly! I'm so grateful for your help.

Mary Jo

[#4]

@Savia So my version of Excel is an older version (Office 2013) that doesn't support the _xlfn function.  However, I opened the file you sent me in the same Excel version, and the formula is working there. Any idea what's up with that? Everything else seems to work like a charm. I copied your formulas exactly and used the CSE command for the arrays.

Mary Jo

[#5]

I only used the newer functions for making the lists of values - the main function is backwards-compatible so should be working fine. If you added more items later on then you'd need to update the labels as I describe but the rest should work fine :)