I have 3 column, A = Date, unsorted; B = Value, unsorted: C = Balance, calculated.
I am trying to find the smallest number in column c when the sheet is sorted by Date, (A assending), and the date associated with it.
I can get it but the process is cumbersome. (see attached sample) … I was thinking I could write a macro to do the copying, sorting and selecting but … I have no idea if a macro would accomplish what I am trying to do.
Assistance would be appreciated.
Are you looking for =OFFSET($A$4;MATCH(SMALL(C5:C23;1);C5:C23;0);0;1;1)?
The formulas in G3 & K3 can be used only after the sorts noted are completed. They do result in the answer I am looking for but I do not know how to automate the coping and sorting and selecting as shown about the data in the sample sheet.
Possible with @anon73440385 help:
In A2: =OFFSET(A4;MATCH(MIN(C5:C23);C5:C23;0);0;1;1) and in C2: =MIN(C5:C23).
SMALL(C5:C23;1) is the same that MIN(C5:C23).Thank you for your input, but if you insert these in cell A2 and C2 you get the results based on un-sorted data. A2 would equal 1/24/21 and C2 would equal -$153.49. The results are correct but the data it is using is unsorted (date). The solution offered would work only if used in cell E2 & G2( 1/29/21 & $21.51 respectively). Using ‘=min’ is a new suggestion and works with the unsorted balance so only 1 sort needs to be done before the 2 formulas will work in E2 & G2. Thank you for that.
Now the only thing I need to overcome is an ‘auto-sort’ of col A and col B based on col A (date) order. Any suggestions on that one?
On your sample, getting data sorted in another range with formulas, so no need to copy and sort.
Marked the lower balance with a conditional format.
Also, a pivot table to obtain the daily balance.
This looks promising … let me work with it for a day or so and I will get back to you. … … Thanks
I am going to kind of start over again… I do not know how to do Pivot Table and I did not know where the formulas you gave me were suppose to go. I am going to repost a new question, “Don’t know how to make it work” I think will be the name. Thank you so very much for your help here.
