I want to divide a single number between multiple cells

[kingfisher225]

Hello, I want to take a singular number say 300,000 and divide it between multiple cells. I am looking to make each cells value 1400. Theoretically this would break it down for me and then leave one at the end that would have an odd number. not really sure what to do

 

[lrobbo314]

*Deleted

 

[lrobbo314]

How about this?

Sub THK()
Dim stp As Long: stp = 1400
Dim Total As Long: Total = 300000
Dim c As Long: c = Total / stp
Dim r As Long: r = Total Mod stp
Dim lr As Long: lr = c + 1

Range("A1:A" & lr - 1).Value2 = stp
Range("A" & lr).Value = r

End Sub

 

[CA_Punit]

So say 300000 is divided by 1400 you would have 214 cells with 1400 and remainder 400. Do you want 214 cell to represent 1400 and one 400????

It will be a formula based solution??

 

[kingfisher225]

So say 300000 is divided by 1400 you would have 214 cells with 1400 and remainder 400. Do you want 214 cell to represent 1400 and one 400????

It will be a formula based solution??

yes exactly

 

[lrobbo314]

Book1
	C	D	E	F
1	Number:	300000		1400
2	Divisor:	1400		1400
3	Cycles:	214		1400
4				1400
5				1400
6				1400
7				1400
8				1400
9				1400
10				1400
11				1400
12				1400
13				1400
14				1400
15				1400
Sheet4

Cell Formulas
Range	Formula
D3	D3	=INT(D1/D2)
F1:F15	F1	=IF(ROW()>$D$3+1,"",IF((ROW()-1)<$D$3,1400,$D$1-($D$2*(ROW()-1))))

 

[sandy666]

just for fun with Power Query
it can be optimised

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Int = Table.AddColumn(Source, "Div", each Number.IntegerDivide([Column1], [Column2]), Int64.Type),
    Last = Table.AddColumn(Int, "Last", each [Column1]-[Column2]*[Div]),
    Grp = Table.Group(Table.ExpandListColumn(Table.AddColumn(Last, "Custom.1", each {1..214}), "Custom.1"), {"Column1"}, {{"Count", each _, type table}}),
    Extract1400 = Table.TransformColumns(Table.AddColumn(Grp, "Custom", each [Count][Column2]), {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract1400, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), Int[Div]{0}),
    ExtractLast = Table.TransformColumns(Table.AddColumn(Split, "Custom", each List.Distinct([Count][Last])), {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    ExtractLast

Column1	Column2
300000	1400
Column1	Custom.1	Custom.2	Custom.3	Custom.4	Custom.5	Custom.6	Custom.7	Custom.8	Custom.9	Custom.10	Custom.11	Custom.12	Custom.13	Custom.14	Custom.15	Custom.16	Custom.17	Custom.18	Custom.19	Custom.20	Custom.21	Custom.22	Custom.23	Custom.24	Custom.25	Custom.26	Custom.27	Custom.28	Custom.29	Custom.30	Custom.31	Custom.32	Custom.33	Custom.34	Custom.35	Custom.36	Custom.37	Custom.38	Custom.39	Custom.40	Custom.41	Custom.42	Custom.43	Custom.44	Custom.45	Custom.46	Custom.47	Custom.48	Custom.49	Custom.50	Custom.51	Custom.52	Custom.53	Custom.54	Custom.55	Custom.56	Custom.57	Custom.58	Custom.59	Custom.60	Custom.61	Custom.62	Custom.63	Custom.64	Custom.65	Custom.66	Custom.67	Custom.68	Custom.69	Custom.70	Custom.71	Custom.72	Custom.73	Custom.74	Custom.75	Custom.76	Custom.77	Custom.78	Custom.79	Custom.80	Custom.81	Custom.82	Custom.83	Custom.84	Custom.85	Custom.86	Custom.87	Custom.88	Custom.89	Custom.90	Custom.91	Custom.92	Custom.93	Custom.94	Custom.95	Custom.96	Custom.97	Custom.98	Custom.99	Custom.100	Custom.101	Custom.102	Custom.103	Custom.104	Custom.105	Custom.106	Custom.107	Custom.108	Custom.109	Custom.110	Custom.111	Custom.112	Custom.113	Custom.114	Custom.115	Custom.116	Custom.117	Custom.118	Custom.119	Custom.120	Custom.121	Custom.122	Custom.123	Custom.124	Custom.125	Custom.126	Custom.127	Custom.128	Custom.129	Custom.130	Custom.131	Custom.132	Custom.133	Custom.134	Custom.135	Custom.136	Custom.137	Custom.138	Custom.139	Custom.140	Custom.141	Custom.142	Custom.143	Custom.144	Custom.145	Custom.146	Custom.147	Custom.148	Custom.149	Custom.150	Custom.151	Custom.152	Custom.153	Custom.154	Custom.155	Custom.156	Custom.157	Custom.158	Custom.159	Custom.160	Custom.161	Custom.162	Custom.163	Custom.164	Custom.165	Custom.166	Custom.167	Custom.168	Custom.169	Custom.170	Custom.171	Custom.172	Custom.173	Custom.174	Custom.175	Custom.176	Custom.177	Custom.178	Custom.179	Custom.180	Custom.181	Custom.182	Custom.183	Custom.184	Custom.185	Custom.186	Custom.187	Custom.188	Custom.189	Custom.190	Custom.191	Custom.192	Custom.193	Custom.194	Custom.195	Custom.196	Custom.197	Custom.198	Custom.199	Custom.200	Custom.201	Custom.202	Custom.203	Custom.204	Custom.205	Custom.206	Custom.207	Custom.208	Custom.209	Custom.210	Custom.211	Custom.212	Custom.213	Custom.214	Custom
300000	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	1400	400

 

[lrobbo314]

A way with new Excel 365 dynamic array function.

Book1
	C	D	E	F	G	H
1				List		Remainder
2	Number:	10000		1400		200
3	Divisor:	1400		1400
4	Cycles:	7		1400
5				1400
6				1400
7				1400
Sheet4

Cell Formulas
Range	Formula
H2	H2	=MOD(D2,D3)
D4	D4	=INT(D2/D3)
F2:F8	F8	=SEQUENCE(D4,,D3,0)
D4	D4	=INT(D2/D3)

 

[lrobbo314]

More fun with Power Query.

let
    Div = 1400,
    Total = 300000,
    Times = Int64.From(Total / Div),
    Source = Table.FromList(List.Transform(List.Generate(()=>0, each _ < Times, each _ + 1), each Div), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Last = Table.InsertRows(Source, Table.RowCount(Source), {[Column1=Number.Mod(Total, Div)]})
in
    Last

 

[sandy666]

More fun with Power Query.

as I said, it can be optimised. I'm too lazy to do it
congrats