Home > Back-end >  Filter and Sort the data from another sheet
Filter and Sort the data from another sheet

Time:10-18

I have a stock xls with two sheets:

TRN HISTORY sheet

with DATE, SYMBOL, UNITS, PRICE, TYPE, OWNED columns enter image description here

DATE SYMBOL UNITS PRICE TYPE OWNED
1.1.2022 O 10 49.6 Dividend Yes
2.2.2022 IRM 20 31.9 Dividend No
3.3.2022 AAL 50 14.0 Growth Yes

STOCKS sheet

with SYMBOL, NAME, STOCK TYPE, UNITS, PRICE columns enter image description here

SYMBOL NAME STOCK TYPE UNITS PRICE
O Realty Income Corp Dividend 10 49.6

I want to filter out only stocks that are marked as Yes in the column OWNED (TRN HISTORY sheet) and show them on the main STOCK sheet with the corresponding price, number of units, and stock type.

I was able to do that via

={"SYMBOL";SORT(FILTER('TRN HISTORY'!B:B;'TRN HISTORY'!F:F="Yes"))}

But I do not know how to get the corresponding values from other columns like the number of units purchased and the price once it's sorted alphabetically.

Here is the demo xls sheet:

enter image description here


UPDATE:

=QUERY(FILTER({'TRN HISTORY'!B:E, {"NAME"; 
 BYROW('TRN HISTORY'!B2:B, LAMBDA(x, GOOGLEFINANCE(x, "name")))}}, 
 REGEXMATCH('TRN HISTORY'!F:F, "(?i)yes|owned")), 
 "select Col1,Col5,Col4,Col2,Col3 order by Col1", 1)

enter image description here

  • Related