SOLVED

just filter specific rows and columns

Occasional Contributor

 

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?

 

ekran_1.pngekran_2.png

 

11 Replies

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

 

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.

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

 

hi @Patrick2788 Ekran görüntüsü 2022-10-30 112409.pngEkran görüntüsü 2022-10-30 112936.png

 

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?

best response confirmed by deniztopcu (Occasional Contributor)
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)))
=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"

@deniztopcu 

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

thank you very much.

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?

 

aaa.png

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.

@Patrick2788 Thank you very much. You are amazing.