Forum Discussion
Extracting non-empty cells from a table based on a value
Hi,
There is an Excel table that we use to monitor reading compliance - columns are people names, rows are document codes, and cells are the reading dates.
Please find below a simplified version:
| John Doe | Jane Doe | John Smith | Gandalf | |
| THIS-123-V10 | 02-11-17 | 05-10-14 | ||
| IS-456-V10 | 01-09-17 | 01-09-17 | ||
| A-789-V10 | 27-09-17 | |||
| TEST-753-V10 | 02-11-17 | 05-10-14 |
I need a quick way to check who has read a specific code and, if possible, on which date. I want a formula where if I put a code, it will detect all cells with a value on the corresponding row and will pull the person's name from the top cell on the same column.
I already have a formula where if I put a person's name and a list of documents, if confirms if they have read it or not, but this is different. I need all results, not just from a limited list.
Thank you
Edit: Forgot to mention - this goes on a separate sheet
Edit 2: Office 2010 :)
8 Replies
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?
- 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.
