Distribute ranges as per Plan

[ayazgreat]

Hi Kris

I need your help to develop codes which will read ranges (Plan Sheet) data from col a to c and distribute these ranges as per distribution data starting from col e2 (first 3 digits of row first are prefix of range) to onward and copy result to result sheet. I know that somehow it is difficult but you can resolve it.
I am attaching an example sheet.

Thanks in advance

[Charles]

Hi,

In this attachment you will see a button for Test.
Click it and check Result sheet.
This code is predicated on the first 3 digit for the start not being with in the entire Start.
And it will only appear 1 time in the start column.
This can be modified if necessary.

[ayazgreat]

thanks charles for your reply but after running your code i get totaly wrong result if you see my attched file result sheet then you will get what result should be there.(kindly compare my result sheet data with your result data)

[ayazgreat]

Could anyone please resolve it ?

[Charles]

Hi,

Too me it appears the code works. In this attachment check the result sheet.
I have your sample and the code sample.
If you tested on actual data let me know.
As mentioned if you are looking at "102" and the start has "102" any place in it the code will send that to the result
sheet. The code does not look at the first 3 characters of the Start. It can be modified.

[ayazgreat]

In your point of view your code works fine but it does not give correct result, if you copy my result sheet data and paste it in the front of your code result data , then you will be able to find difference, let me explain you through an example if a range start number 100101 to end range number 100105 qty = 5 and i say to distribute this range in three diferent IDs , like ID A , to give 2, ID B to 1, and ID C to 2 then above range is distributed like this

IDs Start range End range Qty
a 100101 100102 2
b 100103 100103 1
c 100104 100105 2


It is difficult but can be coded and resolved as given result in my result sheet.

In your code result is like

IDs Start range End range Qty
a 100101 100105 2
b 100101 100105 1
c 100101 100105 2

[ayazgreat]

Could anyone please help ?

[ayazgreat]

Experts, please help to solve this.

[Charles]

HI,

I'm re-attaching my workbook. If you look at the result sheet you will see we differ.
I took the time to look this over. The highlighted areas show where there is a problem.
And as mention my code appears to work.
The qty comes does not come from the column "Count" it comes from the range for the
location within your grid.
If this is wrong please show me the difference.
If you nor longer wish my help and attempts let me know so that I can spend time
helping others.

[ayazgreat]

Charles I really appreciate your effort but the problem is still same, I, maybe, can not make you understand what kind of result I want to have if say to you that if a range start from 1027244000 and ends to 10027246187 = Qty 2188 then as per my data range distribution is like that

my sample
IDs Start Range End Range Qty
A 1027244000 1027244443 444
B 1027244444 1027244943 500
C 1027244944 1027245043 100
D 1027245044 1027245287 244
E 1027245288 1027246087 800
H 1027246088 1027246187 100


in your result why next distribution range is not changed

A 1027244000 1027246187 444
B 1027244000 1027246187 500
C 1027244000 1027246187 100
D 1027244000 1027246187 244
E 1027244000 1027246187 800
H 1027244000 1027246187 100

Why Start Number and End Number is same of ID B,C,D,E,H as same of ID A ? Why isn't range changed after a next ID as per its Plan given Qty, it should be distributed like I am mentioning in my result.

See my attached workbook how am I distributing the same through a formula only for a single range like I have mentioned above range distribution.