Extracting non-empty cells from a table based on a value

Copper Contributor

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,""))}
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?

Thanks

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:

  1. Make your data set as a table, named "MainTable"
    * the headers of MainTable is the first row in your post (the row of name)
  2. Define a name, "NameList"
    =OFFSET(MainTable[#HEADERS],0,1,1,COLUMNS(MainTable[#HEADERS])-1)
  3. Define a name, "CodeList"
    =MainTable[Code]
    * Code is the column name in MainTable for document codes
  4. 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".
  5. Define a name, "SelectedRow"
    =OFFSET(NameList,MATCH(SelectedCode,CodeList,0),0,1)
  6. Define a name, "HitColumnArray"
    =(SelectedRow<>"")*(COLUMN(SelectedRow)-1)
  7. Define a name, "IndexList"
    =SMALL(TRANSPOSE(IF(HitColumnArray=0,"X",HitColumnArray)),ROW())
  8. In Cell B1 on SubSheet, enter array formula
    {=IFERROR(INDEX(NameList,1,IndexList) & " " & INDEX(SelectedRow,1,IndexList),"")}
  9. 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,

  1. This is not a quick way
  2. Array formula solution is slow if your data size is big
  3. May I know your desired layout?

Kaloyan,

 

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.

 

 

@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

@windell505 

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

Screenshot_2023-02-06-13-43-14-458_cn.uujian.browser.jpg

 

like this?

google sheet online search?

@Detlef Lewin did not get this to work in my 365 version.