Forum Discussion
Solved
Another tricky Dynamic Array problem that I can't solve.
With help from this forum I have managed to fix some issues that, to me were very tricky, and have learned a lot of new techniques along the way. But this latest one is again beyond me. Earlier I le...
James_Buist An MMULT approach could be something like this:
=MMULT(--(TOROW(C6:C19)=C27:C29),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))Although, the input and output ranges should both include a start and end date for each period (see attached)...
Brilliant. Both answers work but the second with MMULT is quite a bit faster – and shorter but harder to follow as one has to understand matrix multiplication.
I realised, however, when I read these that I’d actually missed out one element. The vertical (row) filter needs another element added.
Each of the GL Accounts should have a code and some of the accounts will have the same codes so will be aggregated. So this does it by entity but I needed it by entity and GL code. I have added these to the spreadsheet.
With Harun 24Hr’s method I could easily add the extra filter to the filter function which is easy to use and follow. But Same with djclements, after a bit of thinking, I managed to add the second filter to the MMULT resulting in this
=MMULT(--((TOROW(C6:C19)=C27:C29)*(TOROW(A6:A19)=C39)),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))
Thanks to both of you. I am building a collection of different ways to achieve things but keep getting bogged down with things I feel I should be able to do. However, each time I get closer and have solved white a few though I really need to master this MMULT usage as it is quite awesome.
I have put a copy of the modified version in a reply to djclements as it uses his MMULT solution
I realised, however, when I read these that I’d actually missed out one element. The vertical (row) filter needs another element added.
Each of the GL Accounts should have a code and some of the accounts will have the same codes so will be aggregated. So this does it by entity but I needed it by entity and GL code. I have added these to the spreadsheet.
With Harun 24Hr’s method I could easily add the extra filter to the filter function which is easy to use and follow. But Same with djclements, after a bit of thinking, I managed to add the second filter to the MMULT resulting in this
=MMULT(--((TOROW(C6:C19)=C27:C29)*(TOROW(A6:A19)=C39)),MMULT(--E6:M19,(TOCOL(E3:M3)>=E24:I24)*(TOCOL(E4:M4)<=E25:I25)))
Thanks to both of you. I am building a collection of different ways to achieve things but keep getting bogged down with things I feel I should be able to do. However, each time I get closer and have solved white a few though I really need to master this MMULT usage as it is quite awesome.
I have put a copy of the modified version in a reply to djclements as it uses his MMULT solution