staticfluids
New Member
- Joined
- Apr 24, 2024
- Messages
- 8
- Office Version
- 365
- 2021
- 2019
- 2016
- Platform
- Windows
- Mobile
- Web
Hi all, I hope you're doing well.
First of all, sorry for bad explanation as my main language is not English.
I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula. It's quite helping with my daily Excel use. 2 of my latest question was asking about Dynamic Array since I'm very new into this.
I wanted to ask. Refer to this post, I may have the same logic as the OP.
I wanted to distribute value from F:F to fill the value from B:B. I got the logic but turning it into formula is a bit tricky for me that's novice to this area. And I'm not sure if this is increment or not because I'm using a translator app. I think this is only a number distribution.
Anyway, the logic is:
SUMIF the same date on D:D for F:F cell -->
LOOK into the oldest date on D:D for F:F cell -->
take value at 1 cell at F -->
LOOK into the oldest date on A:A for B:B cell -->
SUBTRACT the value at 1 cell at F to the 1 cell at B -->
IF the current F cell has 0 value but the current B cell still have value, continue to the next F cell and DISTRIBUTE the value at F to B until the B value is 0 -->
IF the B value is 0 but F cell still have value, continue to the next B cell and DISTRIBUTE the value at F to B until the F value is 0 --> and so on until the F value on the last cell is 0 or having remaining value.
IF the value at F has decimals or remaining value, leave it at the end of the cell (or in the example, leave it at the top cell).
I think, the formula should be fill only at J:J and K:K. Or maybe from H:H until K:K but having a different formula for each first cell.
Example expected array:
Example if the one last cell on F is having more value than the last cell on B:
I attach the full expected array below, please have a look:
This is a dynamic data; the data is always updated. So I'm looking a way to find a dynamic array formula to avoid manual editing when the data is updated either on A:B or D:F.
Any help would be much appreciated.
Thank you!
First of all, sorry for bad explanation as my main language is not English.
I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula. It's quite helping with my daily Excel use. 2 of my latest question was asking about Dynamic Array since I'm very new into this.
I wanted to ask. Refer to this post, I may have the same logic as the OP.
I wanted to distribute value from F:F to fill the value from B:B. I got the logic but turning it into formula is a bit tricky for me that's novice to this area. And I'm not sure if this is increment or not because I'm using a translator app. I think this is only a number distribution.
Anyway, the logic is:
SUMIF the same date on D:D for F:F cell -->
LOOK into the oldest date on D:D for F:F cell -->
take value at 1 cell at F -->
LOOK into the oldest date on A:A for B:B cell -->
SUBTRACT the value at 1 cell at F to the 1 cell at B -->
IF the current F cell has 0 value but the current B cell still have value, continue to the next F cell and DISTRIBUTE the value at F to B until the B value is 0 -->
IF the B value is 0 but F cell still have value, continue to the next B cell and DISTRIBUTE the value at F to B until the F value is 0 --> and so on until the F value on the last cell is 0 or having remaining value.
IF the value at F has decimals or remaining value, leave it at the end of the cell (or in the example, leave it at the top cell).
I think, the formula should be fill only at J:J and K:K. Or maybe from H:H until K:K but having a different formula for each first cell.
Example expected array:
| Book2 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Month Period | Value | Date | Type | Value | Month Period | Value from B:B | Date | Value from F:F | ||||
| 2 | 01 September 2022 | £ 100.00 | 04 November 2022 | Transfer | £ 400.00 | 01 September 2022 | £ 100.00 | 04 November 2022 | £100.00 | ||||
| 3 | 01 August 2022 | £ 100.00 | 12 July 2022 | Transfer | £ 25.00 | 01 August 2022 | £ 100.00 | 04 November 2022 | £82.50 | ||||
| 4 | 01 July 2022 | £ 100.00 | 02 July 2022 | Transfer | £ 100.00 | 12 July 2022 | £17.50 | ||||||
| 5 | 01 June 2022 | £ 100.00 | 06 June 2022 | Transfer | £ 25.00 | 01 July 2022 | £ 100.00 | 12 July 2022 | £7.50 | ||||
| 6 | 01 May 2022 | £ 100.00 | 26 May 2022 | Transfer | £ 75.00 | 02 July 2022 | £92.50 | ||||||
| 7 | 01 April 2022 | £ 100.00 | 05 May 2022 | Transfer | £ 2.50 | 01 June 2022 | £ 100.00 | 02 July 2022 | £7.50 | ||||
| 8 | 01 March 2022 | £ 100.00 | 05 May 2022 | Transfer | £ 50.00 | 06 June 2022 | £25.00 | ||||||
| 9 | 01 February 2022 | £ 200.00 | 22 April 2022 | Transfer | £ 110.00 | 26 May 2022 | £67.50 | ||||||
| 10 | 01 April 2022 | Transfer | £ 100.00 | 01 May 2022 | £ 100.00 | 26 May 2022 | £7.50 | ||||||
| 11 | 13 March 2022 | Transfer | £ 100.00 | 05 May 2022 | £52.50 | ||||||||
| 12 | 28 February 2022 | Transfer | £ 100.00 | 22 April 2022 | £40.00 | ||||||||
| 13 | 11 February 2022 | Transfer | £ 3.00 | 01 April 2022 | £ 100.00 | 22 April 2022 | £70.00 | ||||||
| 14 | 11 February 2022 | Transfer | £ 6.00 | 01 April 2022 | £30.00 | ||||||||
| 15 | 11 February 2022 | Transfer | £ 1.00 | 01 March 2022 | £ 100.00 | 01 April 2022 | £70.00 | ||||||
| 16 | 26 January 2022 | Transfer | £ 20.00 | 13 March 2022 | £30.00 | ||||||||
| 17 | 01 February 2022 | £ 200.00 | 13 March 2022 | £70.00 | |||||||||
| 18 | 28 February 2022 | £100.00 | |||||||||||
| 19 | 11 February 2022 | £10.00 | |||||||||||
| 20 | 26 January 2022 | £20.00 | |||||||||||
Sheet2 | |||||||||||||
Example if the one last cell on F is having more value than the last cell on B:
| Book2 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Month Period | Value | Date | Type | Value | Month Period | Value from B:B | Date | Value from F:F | ||||
| 2 | 01 September 2022 | £ 100.00 | 04 November 2022 | Transfer | £ 400.00 | 01 September 2022 | £ 100.00 | 04 November 2022 | £217.50 | ||||
| 3 | 01 August 2022 | £ 100.00 | 12 July 2022 | Transfer | £ 25.00 | 01 August 2022 | £ 100.00 | 04 November 2022 | £82.50 | ||||
| 4 | 01 July 2022 | £ 100.00 | 02 July 2022 | Transfer | £ 100.00 | 12 July 2022 | £17.50 | ||||||
| 5 | 01 June 2022 | £ 100.00 | 06 June 2022 | Transfer | £ 25.00 | 01 July 2022 | £ 100.00 | 12 July 2022 | £7.50 | ||||
| 6 | 01 May 2022 | £ 100.00 | 26 May 2022 | Transfer | £ 75.00 | 02 July 2022 | £92.50 | ||||||
| 7 | 01 April 2022 | £ 100.00 | 05 May 2022 | Transfer | £ 2.50 | 01 June 2022 | £ 100.00 | 02 July 2022 | £7.50 | ||||
| 8 | 01 March 2022 | £ 100.00 | 05 May 2022 | Transfer | £ 50.00 | 06 June 2022 | £25.00 | ||||||
| 9 | 01 February 2022 | £ 200.00 | 22 April 2022 | Transfer | £ 110.00 | 26 May 2022 | £67.50 | ||||||
| 10 | 01 April 2022 | Transfer | £ 100.00 | 01 May 2022 | £ 100.00 | 26 May 2022 | £7.50 | ||||||
| 11 | 13 March 2022 | Transfer | £ 100.00 | 05 May 2022 | £52.50 | ||||||||
| 12 | 28 February 2022 | Transfer | £ 100.00 | 22 April 2022 | £40.00 | ||||||||
| 13 | 11 February 2022 | Transfer | £ 3.00 | 01 April 2022 | £ 100.00 | 22 April 2022 | £70.00 | ||||||
| 14 | 11 February 2022 | Transfer | £ 6.00 | 01 April 2022 | £30.00 | ||||||||
| 15 | 11 February 2022 | Transfer | £ 1.00 | 01 March 2022 | £ 100.00 | 01 April 2022 | £70.00 | ||||||
| 16 | 26 January 2022 | Transfer | £ 20.00 | 13 March 2022 | £30.00 | ||||||||
| 17 | 01 February 2022 | £ 200.00 | 13 March 2022 | £70.00 | |||||||||
| 18 | 28 February 2022 | £100.00 | |||||||||||
| 19 | 11 February 2022 | £10.00 | |||||||||||
| 20 | 26 January 2022 | £20.00 | |||||||||||
Sheet3 | |||||||||||||
I attach the full expected array below, please have a look:
| Book2 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Month Period | Value | Date | Type | Value | Month Period | Value from B:B | Date | Value from F:F | ||||
| 2 | 01 April 2024 | £ 200.00 | 18 April 2024 | Transfer | £ 51.75 | 01 April 2024 | £ 200.00 | 18 April 2024 | £51.75 | ||||
| 3 | 01 March 2024 | £ 200.00 | 17 April 2024 | Transfer | £ 465.75 | 17 April 2024 | £148.25 | ||||||
| 4 | 01 February 2024 | £ 100.00 | 21 February 2024 | Transfer | £ 465.00 | 01 March 2024 | £ 200.00 | 17 April 2024 | £200.00 | ||||
| 5 | 01 January 2024 | £ 100.00 | 12 February 2024 | Transfer | £ 400.00 | 01 February 2024 | £ 100.00 | 17 April 2024 | £100.00 | ||||
| 6 | 01 December 2023 | £ 200.00 | 24 January 2024 | Transfer | £ 200.00 | 01 January 2024 | £ 100.00 | 17 April 2024 | £17.50 | ||||
| 7 | 01 November 2023 | £ 200.00 | 26 September 2023 | Transfer | £ 200.00 | 21 February 2024 | £82.50 | ||||||
| 8 | 01 October 2023 | £ 100.00 | 28 May 2023 | Transfer | £ 300.00 | 01 December 2023 | £ 200.00 | 21 February 2024 | £200.00 | ||||
| 9 | 01 September 2023 | £ 100.00 | 31 March 2023 | Transfer | £ 100.00 | 01 November 2023 | £ 200.00 | 21 February 2024 | £182.50 | ||||
| 10 | 01 August 2023 | £ 200.00 | 30 January 2023 | Transfer | £ 100.00 | 12 February 2024 | £17.50 | ||||||
| 11 | 01 July 2023 | £ 200.00 | 10 December 2022 | Transfer | £ 100.00 | 01 October 2023 | £ 100.00 | 12 February 2024 | £100.00 | ||||
| 12 | 01 June 2023 | £ 200.00 | 04 November 2022 | Transfer | £ 400.00 | 01 September 2023 | £ 100.00 | 12 February 2024 | £100.00 | ||||
| 13 | 01 May 2023 | £ 100.00 | 12 July 2022 | Transfer | £ 25.00 | 01 August 2023 | £ 200.00 | 12 February 2024 | £182.50 | ||||
| 14 | 01 April 2023 | £ 100.00 | 02 July 2022 | Transfer | £ 100.00 | 24 January 2024 | £17.50 | ||||||
| 15 | 01 March 2023 | £ 100.00 | 06 June 2022 | Transfer | £ 25.00 | 01 July 2023 | £ 200.00 | 24 January 2024 | £182.50 | ||||
| 16 | 01 February 2023 | £ 100.00 | 26 May 2022 | Transfer | £ 75.00 | 26 September 2023 | £17.50 | ||||||
| 17 | 01 January 2023 | £ 100.00 | 05 May 2022 | Transfer | £ 2.50 | 01 June 2023 | £ 200.00 | 26 September 2023 | £182.50 | ||||
| 18 | 01 December 2022 | £ 100.00 | 05 May 2022 | Transfer | £ 50.00 | 28 May 2023 | £17.50 | ||||||
| 19 | 01 November 2022 | £ 100.00 | 22 April 2022 | Transfer | £ 110.00 | 01 May 2023 | £ 100.00 | 28 May 2023 | £100.00 | ||||
| 20 | 01 October 2022 | £ 100.00 | 01 April 2022 | Transfer | £ 100.00 | 01 April 2023 | £ 100.00 | 28 May 2023 | £100.00 | ||||
| 21 | 01 September 2022 | £ 100.00 | 13 March 2022 | Transfer | £ 100.00 | 01 March 2023 | £ 100.00 | 28 May 2023 | £82.50 | ||||
| 22 | 01 August 2022 | £ 100.00 | 28 February 2022 | Transfer | £ 100.00 | 31 March 2023 | £17.50 | ||||||
| 23 | 01 July 2022 | £ 100.00 | 11 February 2022 | Transfer | £ 3.00 | 01 February 2023 | £ 100.00 | 31 March 2023 | £82.50 | ||||
| 24 | 01 June 2022 | £ 100.00 | 11 February 2022 | Transfer | £ 6.00 | 30 January 2023 | £17.50 | ||||||
| 25 | 01 May 2022 | £ 100.00 | 11 February 2022 | Transfer | £ 1.00 | 01 January 2023 | £ 100.00 | 30 January 2023 | £82.50 | ||||
| 26 | 01 April 2022 | £ 100.00 | 26 January 2022 | Transfer | £ 20.00 | 10 December 2022 | £17.50 | ||||||
| 27 | 01 March 2022 | £ 100.00 | 01 December 2022 | £ 100.00 | 10 December 2022 | £82.50 | |||||||
| 28 | 01 February 2022 | £ 200.00 | 04 November 2022 | £17.50 | |||||||||
| 29 | 01 October 2023 | £ 100.00 | 01 November 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||
| 30 | 01 September 2023 | £ 100.00 | 01 October 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||
| 31 | 01 August 2023 | £ 200.00 | 01 September 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||
| 32 | 01 July 2023 | £ 200.00 | 01 August 2022 | £ 100.00 | 04 November 2022 | £82.50 | |||||||
| 33 | 01 June 2023 | £ 200.00 | 12 July 2022 | £17.50 | |||||||||
| 34 | 01 May 2023 | £ 100.00 | 01 July 2022 | £ 100.00 | 12 July 2022 | £7.50 | |||||||
| 35 | 01 April 2023 | £ 100.00 | 02 July 2022 | £92.50 | |||||||||
| 36 | 01 March 2023 | £ 100.00 | 01 June 2022 | £ 100.00 | 02 July 2022 | £7.50 | |||||||
| 37 | 01 February 2023 | £ 100.00 | 06 June 2022 | £25.00 | |||||||||
| 38 | 01 January 2023 | £ 100.00 | 26 May 2022 | £67.50 | |||||||||
| 39 | 01 December 2022 | £ 100.00 | 01 May 2022 | £ 100.00 | 26 May 2022 | £7.50 | |||||||
| 40 | 01 November 2022 | £ 100.00 | 05 May 2022 | £52.50 | |||||||||
| 41 | 01 October 2022 | £ 100.00 | 22 April 2022 | £40.00 | |||||||||
| 42 | 01 September 2022 | £ 100.00 | 01 April 2022 | £ 100.00 | 22 April 2022 | £70.00 | |||||||
| 43 | 01 August 2022 | £ 100.00 | 01 April 2022 | £30.00 | |||||||||
| 44 | 01 July 2022 | £ 100.00 | 01 March 2022 | £ 100.00 | 01 April 2022 | £70.00 | |||||||
| 45 | 01 June 2022 | £ 100.00 | 13 March 2022 | £30.00 | |||||||||
| 46 | 01 May 2022 | £ 100.00 | 01 February 2022 | £ 200.00 | 13 March 2022 | £70.00 | |||||||
| 47 | 01 April 2022 | £ 100.00 | 28 February 2022 | £100.00 | |||||||||
| 48 | 01 March 2022 | £ 100.00 | 11 February 2022 | £10.00 | |||||||||
| 49 | 01 February 2022 | £ 200.00 | 26 January 2022 | £20.00 | |||||||||
| 50 | 01 October 2023 | £ 100.00 | 12 February 2024 | £100.00 | |||||||||
| 51 | 01 September 2023 | £ 100.00 | 12 February 2024 | £100.00 | |||||||||
| 52 | 01 August 2023 | £ 200.00 | 12 February 2024 | £182.50 | |||||||||
| 53 | 24 January 2024 | £17.50 | |||||||||||
| 54 | 01 July 2023 | £ 200.00 | 24 January 2024 | £182.50 | |||||||||
| 55 | 26 September 2023 | £17.50 | |||||||||||
| 56 | 01 June 2023 | £ 200.00 | 26 September 2023 | £182.50 | |||||||||
| 57 | 28 May 2023 | £17.50 | |||||||||||
| 58 | 01 May 2023 | £ 100.00 | 28 May 2023 | £100.00 | |||||||||
| 59 | 01 April 2023 | £ 100.00 | 28 May 2023 | £100.00 | |||||||||
| 60 | 01 March 2023 | £ 100.00 | 28 May 2023 | £82.50 | |||||||||
| 61 | 31 March 2023 | £17.50 | |||||||||||
| 62 | 01 February 2023 | £ 100.00 | 31 March 2023 | £82.50 | |||||||||
| 63 | 30 January 2023 | £17.50 | |||||||||||
| 64 | 01 January 2023 | £ 100.00 | 30 January 2023 | £82.50 | |||||||||
| 65 | 10 December 2022 | £17.50 | |||||||||||
| 66 | 01 December 2022 | £ 100.00 | 10 December 2022 | £82.50 | |||||||||
| 67 | 04 November 2022 | £17.50 | |||||||||||
| 68 | 01 November 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||||
| 69 | 01 October 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||||
| 70 | 01 September 2022 | £ 100.00 | 04 November 2022 | £100.00 | |||||||||
| 71 | 01 August 2022 | £ 100.00 | 04 November 2022 | £82.50 | |||||||||
| 72 | 12 July 2022 | £17.50 | |||||||||||
| 73 | 01 July 2022 | £ 100.00 | 12 July 2022 | £7.50 | |||||||||
| 74 | 02 July 2022 | £92.50 | |||||||||||
| 75 | 01 June 2022 | £ 100.00 | 02 July 2022 | £7.50 | |||||||||
| 76 | 06 June 2022 | £25.00 | |||||||||||
| 77 | 26 May 2022 | £67.50 | |||||||||||
| 78 | 01 May 2022 | £ 100.00 | 26 May 2022 | £7.50 | |||||||||
| 79 | 05 May 2022 | £52.50 | |||||||||||
| 80 | 22 April 2022 | £40.00 | |||||||||||
| 81 | 01 April 2022 | £ 100.00 | 22 April 2022 | £70.00 | |||||||||
| 82 | 01 April 2022 | £30.00 | |||||||||||
| 83 | 01 March 2022 | £ 100.00 | 01 April 2022 | £70.00 | |||||||||
| 84 | 13 March 2022 | £30.00 | |||||||||||
| 85 | 01 February 2022 | £ 200.00 | 13 March 2022 | £70.00 | |||||||||
| 86 | 28 February 2022 | £100.00 | |||||||||||
| 87 | 11 February 2022 | £10.00 | |||||||||||
| 88 | 26 January 2022 | £20.00 | |||||||||||
Sheet1 | |||||||||||||
This is a dynamic data; the data is always updated. So I'm looking a way to find a dynamic array formula to avoid manual editing when the data is updated either on A:B or D:F.
Any help would be much appreciated.
Thank you!
