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

[Steve_H2470]

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 subtotal function in the sort to report to a cell directly below the table, but also need to do text.

[#2]

@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?

[#3]

@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

[#4]

@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.

[#5]

@Steve_H2470 

If you have access to Lambda, this may work for you.  All you have to do is provide the name of the table in the syntax:  Table1[#All].

=LET(
    t, Table1[#All],
    records, DROP(t, 1),
    header, TAKE(t, 1),
    m, MAP(records, LAMBDA(e, IF(SUBTOTAL(103, e) = 1, e, ""))),
    filtered, FILTER(m, TAKE(m, , 1) <> ""),
    VSTACK(header, filtered)
)