Hi, How do I extract data from many cells into 1 cell? I want the data in 1 cell for each tableName and the ColumnName values to follow. Eg:

(Apples,Red,Green,Blue,Orange) to display from the below table in the same cell. And then
(Bananas,Red,Green,Blue,Pink)
(Grapes,Green, Blue,Pink)
(Pears,Red)

each () being a single cell.

tableName ColumnName
Apples Red
Apples Green
Apples Blue
Apples Orange
Bananas Red
Bananas Green
Bananas Blue


Bananas Pink
Grapes Green
Grapes Blue
Grapes Pink
Pears Red

Thanks in advance.

Try the single-celled formula:
2023-06-09_094502.jpg


A little shorter: I'm sure it could be streamlined more…


Edit: Apologies! I just saw you're using Excel 2016.

With Excel 2016 you have Power Query built-in so in the attached, you can change the data in the blue table on the left, then right-click the green table on the right and choose Refresh, to update the results.


2023-06-09_103056.jpg

Attached Files

msofficeforums50958.xlsx (16.9 KB, 3 views)

Hi p45cal,

Thank you very much for your help. I don't know much about Power Query. How did you tell it to group the info in the blue table? I down loaded your spreadsheet but it's saying external data connection is disabled?

Thanks again.

Usually, Excel asks you if you want to enable it, to which you answer that you want it enabled. In this case there are no external connections, it's just what Excel does!
Come back if it doesn't ask the question; it may be some security setting...

Hi p45cal,

I've got it up & working and it all works perfectly.

Thank you very much for all your help!