Forum Discussion

Steve_H2470's avatar
Steve_H2470
Copper Contributor
Jan 04, 2023

filtering and reporting to individual cell for text with no VBa code necessary

I have a table that has text and when it's sorted, I need it to report the result to a single cell outside the table. Then i will be able to use in formula's elsewhere. I have accomplished the subtot...
Formulas and Functions
dscheikey's avatar
dscheikey
Bronze Contributor
Jan 06, 2023

Steve_H2470 

Please try the TEXTJOIN() function!

=TEXTJOIN(";",TRUE,A1:A3)

Combines your texts in A1:A3 and separates them with a semicolon.

Is that what you need?

 

  • Steve_H2470's avatar
    Steve_H2470
    Copper Contributor
    Jan 06, 2023

    dscheikey 

    Hi, thanks for the response. But not what I'm looking for.

     

    let me try to explain it better. when using tables, if have obviously several columns and rows all with different data.

    I have a simple sort on the header. What I need is when I sort and get data, I only get one row (that I'm looking for) which is what I want., problem is I need to have a cell below each column (outside of the table) that captures the data in that singular row. I have it working just fine for numbers, using the subtotal(109, etc...but need it to also do text in the three columns that i have text in.

    I hope that clarifies things a little. I have looked all around and really really don't want to get into VBA code to make this work.

     

    Any help would be appreciated.

     

    Thanks,

    Steve Hamilton

    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Jan 06, 2023

      Steve_H2470 

      Hello Steve, I hope I have understood your question correctly. I have written a function that gives you the filtered value.

      =INDEX(myTable[HeaderText],XMATCH(1,SUBTOTAL(3,OFFSET(C1,ROW(myTable[HeaderText])-1,0))))

      I have attached my example document. If more than one value is filtered, the function returns the first hit. Therefore, I have built a query in front of it that checks whether only one value is filtered.

      =IF(SUBTOTAL(3,myTable[HeaderText])=1,then,else)

      In combination with filters, you can also output several hits.

      =FILTER(myTable[HeaderText],SUBTOTAL(3,OFFSET(C1,ROW(myTable[HeaderText])-1,0)),"")

      Take a look at my example to see if it answers your question.

Resources