SOLVED

# Displaying most recent value in a horizontal array?

Copper Contributor

# 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.

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)

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

# Re: Displaying most recent value in a horizontal array?

@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

# Re: Displaying most recent value in a horizontal array?

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

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

# Re: Displaying most recent value in a horizontal array?

@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