Divide the numbers on basis of fixed size

[amit_gupta123]

Hii Gurus

I have query wherein where i have combined volumes in different segment. I have fixed file size of 2500. I want to divide the different segment data on basis of fixed file size

Type Total Number of files
A 5342 3
B 3245 3
C 2000 1
The desired result is
File name Size
A1 2500
A2 2500
A3 342
B1 2500
B2 745
C1 2000

File is also attached
Regards

 

[shrivallabha]

I do not understand your requirement.

Do you want to split data in the files? Or you just want Excel formula or code to work out the number of files as you have shown in the attached spreadsheet?

 

[amit_gupta123]

Sir number of files I can make out by dividing total with 2500 and using roundup. I want data get split into different files. It will be displayed like A1 as 2500 then A2 2500 and remaining in A3

 

[shrivallabha]

Here is one formula based approach. I have used lots of helper columns and cells but the formulas are simpler.

The colors and steps are matched so that should give you clear idea what I was thinking. Of course, the approach can be improved. Or simple button click VBA solution can also be used.

Edit: I missed attaching the file. Now it is attached.

 

[amit_gupta123]

Sir thanks a ton...it is really amazing.... i really wish to learn the excel magic like you...hats off

 

[shrivallabha]

Glad to know that it works for you