just filter specific rows and columns

[deniztopcu]

Hi,

I want to filter only certain rows and columns. It may be on a different path. (vloop, hloop) but how can I do it in short way like filter.
The table in the first picture is the result I got with the filter function from the second picture.
How can I extract data for row headers that are only Blue scanned?

[#2]

@deniztopcu 

Try this:

=LET(pullrows,CHOOSEROWS(Combined_tblFinancials,IFERROR(XMATCH(Sayfa1!A2:A148,Combined_tblFinancials[itemCode]),0)),CHOOSECOLS(pullrows,SEQUENCE(,6,2,2)))

Without access to CHOOSECOLS/ROWS, you might use:

=INDEX(Combined_tblFinancials,IFERROR(XMATCH(Sayfa1!A2:A148,Combined_tblFinancials[itemCode]),0),SEQUENCE(,6,2,2))

[#3]

I did not implement what you said. I uploaded my excel file in its original form. I have re-uploaded the file if you want to help.

[#4]

@deniztopcu 

Thanks for sharing the workbook.

Here is the formula. I kept your filter but added CHOOSEROWS to pull the rows that match your subtotals.  The subtotals are stored as a constant in the named item 'rowheaders'

=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),CHOOSEROWS(filtered,XMATCH(RowHeaders,TAKE(filtered,,1))))

[#5]

hi @Patrick2788 

Thank you for your efforts, but I think there are functions that are not supported by my excel.

in the code it is showing this message: "_xlfn."

#AD? = #NAME? error

described on this site:https://support.microsoft.com/tr-tr/office/sorun-bir-xlfn-%C3%B6n-ek-bir-form%C3%BCl%C3%BCn-%C3%B6n%...

What path should I follow?

[#6 · Accepted solution]

@deniztopcu 

Try this:

=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),INDEX(filtered,XMATCH(RowHeaders,INDEX(filtered,,1)),SEQUENCE(,7)))

[#7]

=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),INDEX(filtered,XMATCH(RowHeaders,INDEX(filtered,,1)),SEQUENCE(,7)))
=INDEX(Combined_tblFinancials,IFERROR(XMATCH(Sayfa1!A2:A148,Combined_tblFinancials[itemCode]),0),SEQUENCE(,6,2,2))
Now I can use both. thank you for all your effort.



Why don't I have access to these functions?
"CHOOSECOLS/ROW/TAKE"

[#8]

@deniztopcu 

Glad it worked! Those functions are only available in 365 and Excel web app.

[#9]

thank you very much.

[#10]

Hi @Patrick2788;

Did you manually enter the contents of the "Rowsheaders" constant?
This is not a problem, but others need an address.
In this, it writes directly to the line headers.
Is there a way to do this or did you type it manually?

[#11]

You could run UNIQUE on the item column then select the formula in the formula bar and press F9. This will give you the constant to put in a named item.

[#12]

@Patrick2788 Thank you very much. You are amazing.