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,""))}
Kaloyan Stoyanov's avatar
Kaloyan Stoyanov
Copper Contributor
Nov 30, 2017
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
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Nov 30, 2017

    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.

     

     

  • Willy Lau's avatar
    Willy Lau
    Iron Contributor
    Nov 30, 2017

    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?

Resources