Forum Discussion
Extracting non-empty cells from a table based on a value
Kaloyan,
is this what you had in mind?
| John Doe | Jane Doe | John Smith | Gandalf | |||
| THIS-123-V10 | 02.11.2017 | 05.10.2014 | John Doe, Gandalf | |||
| IS-456-V10 | 01.09.2017 | 01.09.2017 | Jane Doe, John Smith | |||
| A-789-V10 | 27.09.2017 | John Smith | ||||
| TEST-753-V10 | 02.11.2017 | 05.10.2014 | John Doe, Gandalf |
{=TEXTJOIN(", ",TRUE,IF(($B$2:$E$5)*($A$2:$A$5=A2),$B$1:$E$1,""))}Detlef_Lewin did not get this to work in my 365 version.
Detlef_Lewin Hi, can i take this on my 2013 excel?i badly need it for my project.. if it cant make it to my excel then how this works in google sheet?i seldomly using that google sheet
like this?
google sheet online search?
Early on in 2019 I decided that my Office 2010 was unfit for any purpose I had, so spent some of my income on 365 insider beta. Your problem would result in
= LET( SelectNamesλ, LAMBDA(d, TEXTJOIN(", ", ,FILTER(names, d, ""))), selectedNames, BYROW(readingDates, SelectNamesλ), HSTACK(documentCodes, selectedNames) )I have read that Google has implemented Lambda but I have never used sheets
- Hi Detlef,
I'm using 2010 :) TEXTJOIN is a 2016-only formula unfortunately
In general this could be an option if it worked for me. I need to have the formula on a separate sheet, but this is something that's easily adjustable.
I was imaging it that it would read code from a editable cell and then return different names in separate cells, but I'm not convinced if it can be achieved.
Is there a formula that could work in Office 2010?
ThanksKaloyan,
a formula solution would need as many helper columns as your original data. And it would be an array formula - something like Willy's solution.
Is It possible to show us the desired layout so that we can provide you some suggestions?
I can only think of array formula solution, but it will be very slow if your data is huge.
My steps:
- Make your data set as a table, named "MainTable"
* the headers of MainTable is the first row in your post (the row of name) - Define a name, "NameList"
=OFFSET(MainTable[#HEADERS],0,1,1,COLUMNS(MainTable[#HEADERS])-1)
- Define a name, "CodeList"
=MainTable[Code]
* Code is the column name in MainTable for document codes - Define a name, "SelectedCode"
=SubSheet!$A$1
This is what I guess you have a cell to select a document code in anther sheet, "SubSheet". - Define a name, "SelectedRow"
=OFFSET(NameList,MATCH(SelectedCode,CodeList,0),0,1)
- Define a name, "HitColumnArray"
=(SelectedRow<>"")*(COLUMN(SelectedRow)-1)
- Define a name, "IndexList"
=SMALL(TRANSPOSE(IF(HitColumnArray=0,"X",HitColumnArray)),ROW())
- In Cell B1 on SubSheet, enter array formula
{=IFERROR(INDEX(NameList,1,IndexList) & " " & INDEX(SelectedRow,1,IndexList),"")} - Use Fill-Handle to fill Cell B1 content to SubSheet!$B$1:$B$100
* I assume name size ~ 100.
I know that this is not a quick way. However, the defined names are just for you to follow the steps a bit easier. The defined names can be grouped into a single formula.
Again,
- This is not a quick way
- Array formula solution is slow if your data size is big
- May I know your desired layout?
- Make your data set as a table, named "MainTable"
