SOLVED

Displaying most recent value in a horizontal array?

Copper Contributor

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.

Ex:

TODAY'S DATE: July 4th 2024

04-Jul$14.00$45.00 
78.00​
18-May$63.00$86.00
$92.00​
 
04-Jul$134.00$34.00$50.00 
04-Jul$25.00 
 


Output:

78
50
25

(Summed up this would be 153)

Thanks in advance!

2 Replies
best response confirmed by J-Des000 (Copper Contributor)
Solution

@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 ResultsSample Results

@djclements this method works great, thanks again!
1 best response

Accepted Solutions
best response confirmed by J-Des000 (Copper Contributor)
Solution

@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 ResultsSample Results

View solution in original post