Complex Formula Value Distribute

[aqeelnokia99]

Hi Experts i try to Distribute Value from Table 1 Until its Reached Value 1000000 if its Reached Write Remaining amount and start from again from Remain amount for Example
if you see Result from Value 1130 to 465001 Total is 979026 to Reach @ 1000000 we need more 1000000-979026 =20974 This amount will come from Table 1 SR # 5 Which is 250000 After That is you see Result Table next value is 229026 which = to 250000-20974 so on in Simple Distribute Value of table 1 Value Until its Reached 1000000 if its Possible with formula or Vba will Very Big Thanks

Table 1				1000000
SR#	Euros		Result
1	1130			1130
2	171361			171361
3	341534			341534
4	465001			465001
5	250000			20974	This Part is Remaing Balance to Reach amount 1000000
6	390854			229026	Start from again from 250000-20974
7	307572			390854
8	710127			307572
9	600000			72548
10	260208			637579
11	500000			362421
12	274438

 

[DanteAmor]

Hi @aqeelnokia99. Thanks for posting on the forum.

if its Possible with formula or Vba

I show you a possible solution with VBA. (Help columns are not required)
Important. The values are read from cells C3 and up to the last row with values from column C, the result in cell G3 onwards. If you modify the source or destination cells, we need to adjust the macro.

Sub Distribute_Value()
  Dim i As Long, k As Long
  Dim nTop As Double, nSum As Double
  Dim a As Variant, b As Variant
 
  nTop = 1000000
  a = Range("C3", Range("C" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1) * 3, 1 To 1)
 
  For i = 1 To UBound(a)
    nSum = nSum + a(i, 1)
    k = k + 1
    If nSum < nTop Then
      b(k, 1) = a(i, 1)
    ElseIf nSum = nTop Then
      b(k, 1) = a(i, 1)
      nSum = 0
    Else
      b(k, 1) = nTop - (nSum - a(i, 1))
      b(k + 1, 1) = a(i, 1) - b(k, 1)
      nSum = b(k + 1, 1)
      k = k + 1
    End If
  Next
  Range("G3").Resize(UBound(b, 1)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[aqeelnokia99]

This Look Awesome Thanks a lot

 

[aqeelnokia99]

Hopefully now you should not have any problem :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						01/04/2023	1,000,000
2	SR#	Date	Euros	Exchange Rate	Balance Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	08/11/2022	200,000	223.94			200,000		Apr-23	200,000	200,000		200,000
4	2	31/01/2023	171,361	288.29			171,361		Apr-23	371,361	171,361
5	3	31/01/2023	341,534	288.29			341,534		Apr-23	712,895	341,534		171,361
6	4	09/02/2023	465,001	290.11	€ 177,896		287,105		Apr-23	1,177,896	287,105	177,896
7	5	13/02/2023	250,000	279.98			177,896		May-23	427,896	250,000		341,534
8	6	13/02/2023	390,854	288.36			250,000		May-23	818,750	390,854
9	7	21/02/2023	307,572	279.43	€ 126,322		390,854		May-23	1,126,322	181,250	126,322	287,105
10	8	27/02/2023	710,127	274.01			181,250		May-23	836,449	710,127		177,896
11	9	09/03/2023	600,000	297.7	€ 436,449		126,322		May-23	1,436,449	163,551	436,449	250,000
12	10	13/03/2023	260,208	302.2			710,127		Jun-23	696,657	260,208
13	11	21/03/2023	500,000	302	€ 196,657		163,551		Jun-23	1,196,657	303,343	196,657	390,854
14	12	28/03/2023	274,438	304.81			436,449		Jul-23	471,095	274,438
15							260,208		Jul-23	471,095	-		181,250
16							303,343		Jul-23	471,095	-		126,322
17							196,657		Aug-23	471,095	-		710,127
18							274,438		Aug-23	471,095	-
19							-		Aug-23	471,095	-		163,551
20							-		Aug-23	471,095	-		436,449
21							-		Aug-23	471,095	-		260,208
22										471,095	-
23										471,095	-		303,343
24										471,095	-		196,657
25										471,095	-		274,438
26										471,095	-
27										471,095	-		-
28										471,095	-
29										471,095	-		-
30										471,095	-
31										471,095	-		-
Sheet1

Cell Formulas
Range	Formula
J3	J3	=IF(J2>$G$1,C3,J2+C3)
K3	K3	=IF(J3>$G$1,IF($G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K2:L$3)=0,C3,$G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K2:L$3)),C3)
L3	L3	=IF($G$1*COUNTIF($J$3:J3,">"&$G$1)-SUM($K$3:K3)=0,C3-K3,"")
M3:M31	M3	=IFERROR(INDEX($K$3:$L$31,1+INT((ROW(A1)-1)/2),MOD(ROW(A1)-1+2,2)+1),"")
J4:J31	J4	=IF(J3>$G$1,C4+L3,J3+C4)
K4:K31	K4	=IF(J4>$G$1,IF($G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:L3)=0,C4,$G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:L3)),C4)
L4:L31	L4	=IF($G$1*COUNTIF($J$3:J4,">"&$G$1)-SUM($K$3:K4)-SUM($L$3:L3)=0,C4-K4,"")
E3:E14	E3	=L3
G3:G24	G3	=IFERROR(INDEX(M:M,SMALL(IF(M$3:M$1000<>"",ROW(M$3:M$1000)), ROWS(M$3:M3))), "")

This look awesome i

 

[aqeelnokia99]

Hi @aqeelnokia99. Thanks for posting on the forum.


I show you a possible solution with VBA. (Help columns are not required)
Important. The values are read from cells C3 and up to the last row with values from column C, the result in cell G3 onwards. If you modify the source or destination cells, we need to adjust the macro.

Sub Distribute_Value()
  Dim i As Long, k As Long
  Dim nTop As Double, nSum As Double
  Dim a As Variant, b As Variant
 
  nTop = 1000000
  a = Range("C3", Range("C" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1) * 3, 1 To 1)
 
  For i = 1 To UBound(a)
    nSum = nSum + a(i, 1)
    k = k + 1
    If nSum < nTop Then
      b(k, 1) = a(i, 1)
    ElseIf nSum = nTop Then
      b(k, 1) = a(i, 1)
      nSum = 0
    Else
      b(k, 1) = nTop - (nSum - a(i, 1))
      b(k + 1, 1) = a(i, 1) - b(k, 1)
      nSum = b(k + 1, 1)
      k = k + 1
    End If
  Next
  Range("G3").Resize(UBound(b, 1)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Awesome Big Thanks

 

[Sanjeev1976]

Thank you for your feedback