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.

[ayazgreat]

Can anybody please solve it here ?

[Charles]

Hi,

Not too split hairs, but in your request you did not specify the result you expected.
From your request it looked as if you just wanted to do what my code does.
Now in your latest example you show that you want to add the Location to the Start and subtract 1.
This would be the End and in the qty would be the info Ie: "444" in the column for qty.
And as of now it appears that I'm the only one trying to help.
We/I can only do what appears to satisfy your request.
Even now with what you posted I still have a question.
That is in your last sample you show "1027244000" in the Start column.
And in the "End" column you have "1027246187" with a Qty of "2801"
Will you be entering the data? Or, do you want the code to do it?

[ayazgreat]

thanks for your reply, regarding to your question, I would like to say , I will be entering data more if you see data example in my post attached workbook sheet plan, you will find lots of Start and End ranges and from column e you will find distribution plan which result will be displayed in result sheet , if you will see carefully result sheet you will find each ID add and subtract 1 as per given data from column e in Plan sheet.

[Charles]

OK, I see that, but, will you be entering data in all of column A and B, C in the Plan sheet?
And you want the code to produce the result as you indicated in the "Result" sheet?

[ayazgreat]

Yes i will be entering data in al o colun a, b c in plan and code produce result as i mentioned in result sheet

[Charles]

ok,

thanks

[ayazgreat]

You are always welcome charles

[ayazgreat]

Charles, one more thing I would like to tell you which you might not have observed in Plan sheet that there are different ranges of same Prefix those are distributed as per each ID given Qty as mentioned below pic example in highlighted ID range, please see its range distribution

493

[Charles]

Hi,

I did not see as you mentioned. Also when looking at your sample how would you know the "E" needs to be entered 2 times.
I do not see any indication it should be done this way.

[ayazgreat]

Because I do work on it , I mean on these ranges , any prefix can have different breakup , I have found some code from a web site through which can read only I'D , a prefix count and zone beside it , if you do not mind , I can share it with a workbook sample and if can you make some amendment to read plan from column e to onward in plan sheet and display result in result ahett instead of plan sheet.

[Charles]

Hi,

Not a problem. Go ahead and attach it.

[ayazgreat]

Please find attached workbook and run macro and see result , Can you make some some changes in it and get required result as mentioned in my first post attached workbook

[ayazgreat]

Charles have got any development

[Charles]

HI,

Sorry to say I do not have a answer. The file you sent has code that I've seen before, but I do not truly
understand its operation. And from what you want it may be beyond my ability.
But I'll still continue with this. Hopefully another member may be able help.

[ayazgreat]

Is there any other member, who can resolve it ?

[Charles]

Hi,


I modified my code.
Check it and let me know what and where its wrong and how to determine the solution to resolve it.

[ayazgreat]

Thank you very much for your efforts, there are still some error regarding to same prefix breakup of range, I have marked and explained these errors in attached file which are in result sheet in row number B25 & B49.

[Charles]

Thanks, I'll take a look.

[Charles]

Hi,

I think I see what you want.
I'll see if I can work something out.

[ayazgreat]

Is there any progress ? Could someone else solve it please ?

[ayazgreat]

kinldy help to resolve it

[Charles]

Hi,

I was able to modify the code in one of your files.
If you change the "Location grid" range the code will need to be updated.
If you plan on this let me know and I'll modify the code so that it may not be necessary to update.

[ayazgreat]

Charles how should I change location that helps you modify code where do you want to keep location grid

[Charles]

Hi,

THe "Location" grid starts in column "F" thru "U". The way I had the code set was to go
"F to "U" and if you added another column say "V" then the code will not work properly because I
pre set the range to "U". In this copy I changed the code so that if you added more columns the code will see it
and not fail. In column "F" you have the "Alpha" characters. If you add more to this the code should still work.
It was the matter of extending the "Columns".
I did not test for this, but you can and let me know.

[ayazgreat]

Wooo, it is amazing you did it what I required, by modifying my provided code, i am very glad , thank you very much Charles, i really appreciate your efforts and time invested by you to resolve it.

[Charles]

Thanks for letting me know.