Rows 10-59, if Col Z has value, list corresponding cell in Col Y & Col AA, concatenated

Hi there,

For Z10:Z59, if there is a value, rather than "", l want to list the corresponding cell in the same row in Column Y, put next to the corresponding cell in the same row for Column AA .

There's a twist though: I want it all in one cell.
I mean, l want the formula in one cell, such that it produces a list for me, within that one cell.
A list of everything in the range Row 10 to Row 59 , that fits the condition.

Hi there, here's a an example:


Column Y Column Z Column AA
red triangle
blue hello square
red hello triangle
grey bye circle
green triangle
blue hello square


Desired result in target cell where the formula is:

blue square
red triangle
grey circle
blue square



All in one cell.

Here is a picture of the cell layout, because i cannot format the text properly on this forum's text editor - please see attachmentAttachment 729047

Omg why is there a "reply to thread" button at the bottom of the text input window, which clears the entire window?

I have for the second thread now, typed out a giant reply, and lost it all, absolutely all gone, by pressing the "Reply to Thread" button.

I'll try again:

The attachment didn't attach. So i must start again for the 3rd time. I thought the OP was simple but l guess not. Sorry. I shall try to explain again.


ROW 1:
Column Y: red
Column Z:
Column AA: triangle

ROW 2:
Column Y: blue
Column Z: hello
Column AA: square

ROW 3:
Column Y: red
Column Z: hello
Column AA: triangle

ROW 4:
Column Y: grey
Column Z: bye
Column AA: circle

ROW 5:
Column Y: green
Column Z:
Column AA: triangle

ROW 6:
Column Y: blue
Column Z: hello
Column AA: square


Desired result: either just show me the value for Column Y if Z has a value, or Column Y concatenated with Column AA.

BUT there's something more: I want all of the results together, no null lines, just the positive results
ALL in one cell.

Is that possible?

Desired result:

either:
blue
red
grey
blue

or col Y and col AA concatenated:

blue square
red triangle
grey circle
blue square

OK i will just contruct the data l have already given

Here it is, it's just the same data as in my replies

Please let me know if you cannot open it

Thanks in advance.

I guess it really is game over for me, at last! I've found something Excel cannot do (without VBA)

To combine all results in one cell with a formula, only MS365 can achieve this with formula like the following.

Formula:

Please Login or Register  to view this content.

t-CPSv-Xp417.png

Hi there thanks.

I think l'll give phatdear the benefit of the doubt as to finding the solution, seeing as l don't have MS365.

ChemistB, yours gave a combo all in one cell by matching one column's value with another on caveat of there being an actual value in one of the columns. That's what l asked for, that's what l got. Unfortunately, it doesn't format the text in a copy-pasteable manner so for me l'll just stick to manual copy-pasting (just 2 or 3 clicks), l just thought it'd be nice to automate it because, well, spreadsheets are our slaves.

Also, the actual real data set is about 240 rows long.

Technically you both did as well as each other. Thanks guys! I think l'm done with my tech challenges for the forseeable future - bye!