I have a stock xls with two sheets:
TRN HISTORY sheet
with DATE, SYMBOL, UNITS, PRICE, TYPE, OWNED columns
| 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
| 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:
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)
