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

 

[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]

Try the following :

Book7 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Table 1			1,000,000
2	SR#	Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	1,130		1,130			1,130	1,130		1,130
4	2	171,361		171,361			172,491	171,361
5	3	341,534		341,534			514,025	341,534		171,361
6	4	465,001		465,001			979,026	465,001
7	5	250,000		20,974			1,229,026	20,974	229,026	341,534
8	6	390,854		229,026			390,854	390,854
9	7	307,572		390,854			698,426	307,572		465,001
10	8	710,127		307,572			1,408,553	301,574	408,553
11	9	600,000		301,574			600,000	600,000		20,974
12	10	260,208		408,553			860,208	260,208		229,026
13	11	500,000		600,000			1,360,208	139,792	360,208	390,854
14	12	274,438		260,208			274,438	274,438
15				139,792			274,438	-		307,572
16				360,208			274,438	-
17				274,438			274,438	-		301,574
18							274,438	-		408,553
19							274,438	-		600,000
20							274,438	-
21							274,438	-		260,208
22							274,438	-
23							274,438	-		139,792
24							274,438	-		360,208
25							274,438	-		274,438
26							274,438	-
27							274,438	-		-
28							274,438	-
29							274,438	-		-
30							274,438	-
31							274,438	-		-
Sheet2

Cell Formulas
Range	Formula
G3:G31	G3	=IF(G2>$D$1,B3,G2+B3)
H3	H3	=IF(G3>$D$1,IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($D2:D$3)=0,B3,$D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($D2:D$3)),B3)
I3:I31	I3	=IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H$3:H3)=0,B3-H3,"")
J3:J31	J3	=IFERROR(INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1),"")
H4:H31	H4	=IF(G4>$D$1,IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H3)=0,B4,$D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H3)),B4)
D3:D17	D3	=IFERROR(INDEX(J:J,SMALL(IF(J$3:J$1000<>"",ROW(J$3:J$1000)), ROWS(J$3:J3))), "")

 

[aqeelnokia99]

Amazing it was very complex Task Big Thanks to Crack it

 

[aqeelnokia99]

Helper Column 4 not working What is range of "Data" in formula

 

[aqeelnokia99]

i found 1 issue after result 307572 Row #10 Next Result must 72548 because to Reach on 1000000 we need more 72548

1000000	1130
	171361
	341534
	465001
	20974
1000000	229026
	390854
	307572
	72548
1000000	637579
	362421

 

[Sanjeev1976]

Helper Column 4 not working What is range of "Data" in formula

$H$3:$I$31

 

[Sanjeev1976]

Try the updated version :

Book7 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	Table 1			1,000,000
2	SR#	Euros		Result			Helper1	Helper2	Helper3	Helper4
3	1	1,130		1,130			1,130	1,130		1,130
4	2	171,361		171,361			172,491	171,361
5	3	341,534		341,534			514,025	341,534		171,361
6	4	465,001		465,001			979,026	465,001
7	5	250,000		20,974			1,229,026	20,974	229,026	341,534
8	6	390,854		229,026			619,880	390,854
9	7	307,572		390,854			927,452	307,572		465,001
10	8	710,127		307,572			1,637,579	72,548	637,579
11	9	600,000		72,548			1,237,579	362,421	237,579	20,974
12	10	260,208		637,579			497,787	260,208		229,026
13	11	500,000		362,421			997,787	500,000		390,854
14	12	274,438		237,579			1,272,225	2,213	272,225
15				260,208			272,225	-		307,572
16				500,000			272,225	-
17				2,213			272,225	-		72,548
18				272,225			272,225	-		637,579
19				-			272,225	-		362,421
20				-			272,225	-		237,579
21							272,225	-		260,208
22							272,225	-
23							272,225	-		500,000
24							272,225	-
25							272,225	-		2,213
26							272,225	-		272,225
27							272,225	-		-
28							272,225	-
29							272,225	-		-
30							272,225	-
31							272,225	-		-
Sheet2

Cell Formulas
Range	Formula
G3:G7	G3	=IF(G2>$D$1,B3,G2+B3)
H3	H3	=IF(G3>$D$1,IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H2:I$3)=0,B3,$D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H2:I$3)),B3)
I3	I3	=IF($D$1*COUNTIF($G$3:G3,">"&$D$1)-SUM($H$3:H3)=0,B3-H3,"")
J3:J31	J3	=IFERROR(INDEX(Data,1+INT((ROW(A1)-1)/COLUMNS(Data)),MOD(ROW(A1)-1+COLUMNS(Data),COLUMNS(Data))+1),"")
H4:H31	H4	=IF(G4>$D$1,IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:I3)=0,B4,$D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:I3)),B4)
I4:I31	I4	=IF($D$1*COUNTIF($G$3:G4,">"&$D$1)-SUM($H$3:H4)-SUM($I$3:I3)=0,B4-H4,"")
G8:G31	G8	=IF(G7>$D$1,B8+I7,G7+B8)
D3:D20	D3	=IFERROR(INDEX(J:J,SMALL(IF(J$3:J$1000<>"",ROW(J$3:J$1000)), ROWS(J$3:J3))), "")

Data refers to Named range = $H$3:$I$31

 

[aqeelnokia99]

Awesome Its working Good Thanks a Lot

 

[aqeelnokia99]

i change Value its give negative Result for some reason

					Apr-23	1000000
SR#	Date	Euros	Exchange Rate	Balance Euros		Result			Helper1	Helper2	Helper3	Helper4
1	08-Nov-22	200000	224			200000		Apr-23	200000	200000		200000
2	31-Jan-23	171361	288			171361		Apr-23	371361	171361
3	31-Jan-23	341534	288			341534		Apr-23	712895	341534		171361
4	09-Feb-23	465001	290.11		1	287105		Apr-23	1177896	287105	177896
5	13-Feb-23	250000	280	€ 229,026		177896		May-23	250000	250000		341534
6	13-Feb-23	390854	288			250000		May-23	640854	390854
7	21-Feb-23	307572	279			390854		May-23	948426	307572		287105
8	27-Feb-23	710127	274	€ 637,579		307572		May-23	1658553	-126322	836449	177896
9	09-Mar-23	600000	297.7	€ 237,579	1	-126322		May-23	1436449	163551	436449	250000
10	13-Mar-23	260208	302.2			836449		Jun-23	696657	260208
11	21-Mar-23	500000	302		1	163551		Jun-23	1196657	303343	196657	390854
12	28-Mar-23	274438	304.81	€ 272,225		436449		Jul-23	471095	274438
						260208		Jul-23	471095	0		307572
					1	303343		Jul-23	471095	0
						196657		Aug-23	471095	0		-126322
						274438		Aug-23	471095	0		836449
						0		Aug-23	471095	0		163551
						0		Aug-23	471095	0		436449
						0		Aug-23	471095	0		260208
					#NUM!	#NUM!		#NUM!	471095	0
					#NUM!	#NUM!		#NUM!	471095	0		303343
					#NUM!	#NUM!		#NUM!	471095	0		196657
									471095	0		274438
									471095	0
									471095	0		0
									471095	0
									471095	0		0
									471095	0
									471095	0		0