Forum Discussion

Kaloyan Stoyanov's avatar
Kaloyan Stoyanov
Copper Contributor
Nov 30, 2017

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 vers...
Formulas & Functions
Requests
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Nov 30, 2017

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,""))}
windell505's avatar
windell505
Copper Contributor
Feb 05, 2023

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

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Feb 06, 2023

     

     

    like this?

    google sheet online search?

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 05, 2023

    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

Resources