CODEWITHSUNDEEP
excelgoogle-sheetsmatchunique
Jerald
Jerald

Reputation: 21

How to get value of a cell based on latest unique date in another column?

I have a table of data which tracks all my expenses. There are 3 main columns mainly Date, Balance and Profit.

I would like to output the data into another table which automatically lists out my latest balance for that day. I tried to google but came up with nothing. Hopefully someone helps me out on this! Thanks alot!

Cell F3 should display 1290 and F4 should display 1818.

enter image description here

For the Daily tracking on column E, i used the formula: =Arrayformula(unique(A3:A))

Upvotes: 2

Views: 478

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

Without a helper column

Please also try the following formula.

=QUERY(ARRAYFORMULA(IF(A2:A=A3:A,,{A2:A,B2:B,C2:C})), 
        "where Col1 is not null")

For a different locale you can use the following version: 

=QUERY(ARRAYFORMULA(IF(A2:A=A3:A;;{A2:A\B2:B\C2:C})); 
        "where Col1 is not null")

enter image description here

Functions used:

Upvotes: 1

zummon
zummon

Reputation: 986

I think you need to use one more column to get the last row of date, so on "D3" put =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(A3:A=A4:A,,A3:A)),COUNTA(A3:A),1) to get the last row of date

then on "E3" put =Arrayformula(FILTER({D3:D,B3:C},ISBLANK(D3:D)=FALSE)) as you were using unique(), let use filter() instead

I hope it'll be helpful It should have more other ways to get this worked, I think this is the easiest solution in my opinion

by the way if you still don't understand you can ask me more

Upvotes: 0

Related Questions