Displaying most recent value in a horizontal array?

Hi, I have an array full of prices and 1 date column. I need to display the most recent value of each row's array that are all equal to today's date. I plan to sum all the numbers eventually. E...

excel

Excel on Mac

office 365

djclements

Jul 04, 2024

J-Des000 You could try the following method to sort each row by column number (descending) to return a single column with the current prices (filtered by date = today):

=LET(
    data, A2:E5,
    dates, TAKE(data,, 1),
    prices, DROP(data,, 1),
    n, COLUMNS(prices),
    cols, SEQUENCE(, n),
    currentPrices, TAKE(WRAPROWS(SORTBY(TOCOL(prices), TOCOL(IF(cols, SEQUENCE(ROWS(prices)))), 1, TOCOL(IF(prices <> "", cols, 0)), -1), n),, 1),
    FILTER(currentPrices, dates = TODAY(), 0)
)

Adjust the data range as needed.

Sample Results

djclements

Bronze Contributor

Jul 04, 2024

J-Des000 You could try the following method to sort each row by column number (descending) to return a single column with the current prices (filtered by date = today):

=LET(
    data, A2:E5,
    dates, TAKE(data,, 1),
    prices, DROP(data,, 1),
    n, COLUMNS(prices),
    cols, SEQUENCE(, n),
    currentPrices, TAKE(WRAPROWS(SORTBY(TOCOL(prices), TOCOL(IF(cols, SEQUENCE(ROWS(prices)))), 1, TOCOL(IF(prices <> "", cols, 0)), -1), n),, 1),
    FILTER(currentPrices, dates = TODAY(), 0)
)

Adjust the data range as needed.

Sample Results

J-Des000

Copper Contributor

Jul 08, 2024

djclements this method works great, thanks again!

Forum Discussion

Displaying most recent value in a horizontal array?

Resources