-1

Question - How could I manage to get the required output (see image below) from the given input? Is it possible without having to write a macro or a script?

Background - Input is transactions from your banks, so there can be multiple transactions on a given day (I just included the balance for each line). Then I want to basically produce a summary of the final balance for the day for each bank account. Understand there is an order issue here, so just assume you take the last transaction on the given day for that bank account based on the order they exist in in the spreadsheet. Then for the output, if you have no transactions on a given day, you need to take the balance for the day before...

Example of what I am after

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
Greg
  • 34,042
  • 79
  • 253
  • 454

1 Answers1

1
=QUERY(A1:C, "select A,sum(B) where C is not null group by A pivot C", 1)

0

=ARRAYFORMULA(QUERY(UNIQUE(IFERROR(VLOOKUP(TRANSPOSE(QUERY(TRANSPOSE(QUERY(A2:C, 
 "select A,C order by A desc", 0)),,999^99)), SORT({TRANSPOSE(QUERY(TRANSPOSE(QUERY(A2:C, 
 "select A,C", 0)),,999^99)), A2:C, ROW(A2:A)}, 5, 0), {2,3,4}, 0))), 
 "select Col1,sum(Col2) where Col3 is not null group by Col1 pivot Col3 label Col1'date'", 0))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • unfortunately this does not give me what I'm after as (a) the amounts have been summed e.g. F2 should have been $160 not $390, and (b) also needed the blank days populated with the last balance available from the days before. Refer my required output table – Greg Jul 31 '19 at 21:26