#1
|
|||
|
|||
Extract data from many cells into 1 cell
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. |
#2
|
||||
|
||||
Try the single-celled formula:
Code:
=BYROW(UNIQUE(Table1[tableName]),LAMBDA(tn,tn & "," & TEXTJOIN(",",TRUE,FILTER(Table1[ColumnName],Table1[tableName]=tn)))) A little shorter: Code:
=BYROW(UNIQUE(Table1[tableName]),LAMBDA(tn,TEXTJOIN(",",TRUE,tn,FILTER(Table1[ColumnName],Table1[tableName]=tn)))) Edit: Apologies! I just saw you're using Excel 2016. |
#3
|
||||
|
||||
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 |
#4
|
|||
|
|||
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. |
#5
|
||||
|
||||
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... |
#6
|
|||
|
|||
Hi p45cal,
I've got it up & working and it all works perfectly. Thank you very much for all your help! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
filling in blank cells with data from cell above | RayK | Excel | 1 | 01-12-2017 04:14 PM |
Combining several cells data into one single cell | H28Sailor | Excel | 6 | 01-09-2017 06:00 AM |
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 | bobznkazoo | Excel | 2 | 03-27-2014 11:14 AM |
How can I fill the below emty cells with above cell data? | Learner7 | Excel | 8 | 06-28-2011 12:10 PM |
How to combine different cells data in one cell? | Learner7 | Excel | 1 | 07-28-2010 12:07 PM |