Start & End Number Further Converted

[Rick Rothstein]

Okay, thanks for the confirmation. I might be able to handle both situations with a single macro. Be patient and give me a little time to work out the details... I'll be back with a solution as soon as I am able to develop one.

I am having a problem with the structure of your data. Going from a "small" chart to a "larger" chart (what your second question asked) should be doable without much problem. However, I see possible structural problems in going from a "large" chart to a "small" chart (what your original question asked) and I do not know what to do about it. Consider this simplified "large" chart...

	I	J	K
1	Start Range	End Range	Qty
2	10000	10399	400
3	10400	10799	400
4	10800	11199	400
5	20000	20399	400
6	20400	20799	400
7	20800	21199	400
8	21200	21599	400
9	21600	21999	400
10	15000	15399	400
11	15400	15799	400
12	15800	16199	400
13	16200	16599	400
14	16600	16999	400

Let's say you wanted to create a chart with range quantities of 2000. The first two shaded areas represent quantities of 2000 each with the last shaded area containing a "left-over" amount of 1200. I do not know how to structure the ranges for a chart having range quantities of 2000 each. Why? Because the 2000 quantities bridge two different ranges with a gap between them... 1000 to 11199 and 20000 to 20799. What would the Start Range and End Range be in the new chart with range quantities of 2000 each? The same problem occurs in the second shaded area where the 2000 quantities bridge across an overlap... 20800 to 21999 and 15000 to 15799. How should the smaller chart look? In other words, what values should go in the blank cells, and just as important, why?

	I	J	K
1	Start Range	End Range	Qty
2	10000		2000
3			2000
4		16199	1200

[ayazgreat]

Going from a "small" chart to a "larger" chart , I always sort data in asscending order as mentioned above data is not in asscending order if there are lots of different ranges in break and I have shown result example im my all attached sheets, furthermore I do not want (in first case) different ranges result to be converted into 2000 but 4000,20000.

Let me tell you a little bit description, I have different big boxes of same and different ranges each box having quantity 20000, in each big box there are 5 small boxes each box having quantity 4000 and at last there are more small 10 boxes having quantity 400.

And my ist question is that if I receive data range (from Start and End range) splitting in 400 qty of different ranges, then I need a macro to convert data of different ranges into 20000 or 4000.

My 2nd question is that If I receive data range (from Start and End range) in 20000 qty then I need a macro to convert data of different ranges into 400.

I am attaching another workbook with 2 different sheets with above mentioned result.

[Rick Rothstein]

You have not said this directly, but I need to know... am I correct in assuming that all full continuous ranges (those without breaks or overlaps in the individual smaller ranges making up that full continuous range) will always contain a total quantity of 100000 (with the exception of the last range which might contain a smaller quantity)?

[ayazgreat]

You have not said this directly, but I need to know... am I correct in assuming that all full continuous ranges (those without breaks or overlaps in the individual smaller ranges making up that full continuous range) will always contain a total quantity of 100000 (with the exception of the last range which might contain a smaller quantity)?

It is difficult to say that.
These ranges are always full continuous some times these are full continuous but dome times these are in breaks
Total quantity is not always 100000 it might more than 100000 or less than .
Like
200000, 20000,12000,4000,8000, but not less than 400 , not in odd numbers .
It runs in 400 figure.

[Rick Rothstein]

It is difficult to say that.
These ranges are always full continuous some times these are full continuous but dome times these are in breaks
Total quantity is not always 100000 it might more than 100000 or less than .
Like
200000, 20000,12000,4000,8000, but not less than 400 , not in odd numbers .

Okay, this brings me back to the problem I was trying to describe two messages back. Let's assume the continuous range spans 4000 and that the next continuous range, also spanning 4000, uses a completely different number set for its range. For example...

Start End Qty
10000 - 11999 - 2000
12000 - 13999 - 2000
20000 - 21999 - 2000
22000 - 23999 - 2000
etc.

Now let's say you wanted to make this into a table showing quantities of 8000... how would the ranges for the first 8000 be labeled? The range of 8000 spans across two non-continuous set of ranges, so what range numbers do I use? If you could guarantee that there was always 100000 in any continuous range set, then for the tables you have indicated you wanted so far, there would be no problem. But as soon a continuous range can span a smaller range, we have to be mindful of asking for larger range tables that span from one continuous range into, or across, a different continuous range. So, in that situation, what should the macro do when labeling the smaller table (larger ranges) constructed from a larger table (smaller ranges) when that larger table (smaller range) has breaks and overlaps in its various ranges that get bridged across by the larger quantity ranges for that new table?

[ayazgreat]

I can not give guarantee that the data I would receive in quantity 100000 of different non continuous ranges , and for this I am looking for a better solution

[Rick Rothstein]

I can not give guarantee that the data I would receive in quantity 100000 of different non continuous ranges , and for this I am looking for a better solution

I am not sure what I should say here. You do see my problem in trying to write the code though, right?

	A	B	C	D	E	F	G
1	Start Range	End Range	Qty		Result: Target = 2000 or less
2	925874000	925874399	400		Start Range	End Range	Qty
3	925874400	925874799	400		925874000	925875999	2000
4	925874800	925875199	400		925876000	???	2000
5	925875200	925875599	400		811877600	811878799	1200
6	925875600	925875999	400
7	925876000	925876399	400
8	925876400	925876799	400
9	811876400	811876799	400
10	811876800	811877199	400
11	811877200	811877599	400
12	811877600	811877999	400
13	811878000	811878399	400
14	811878400	811878799	400

This is your data and data structure, so if you had the table on the left and you were producing the table on the right in real life, what would you put in the cell marked with the question marks? One idea that came to mind was to put both ranges in the same row...

	E	F	G
1	Result: Target = 2000 or less
2	Start Range	End Range	Qty
3	925874000	925875999	2000
4	925876000 811876400	925876799 811876799	2000
5	811877600	811878799	1200

What do you think of this idea?

[ayazgreat]

The result target twenty thousand or less

[Rick Rothstein]

The result target twenty thousand or less

Stop looking at the numbers I am using and look at the structure of the table instead. I could construct a table for 20000, but all that would involve is many more rows of 400 each... I am trying to keep the table small for posting purposes while trying to get you to tell me how to handle the structural problem that occurs when a continuous set of ranges of 400 total less than the target number. Look at my last post, make believe 2000 is a possible target and tell me how you want the program you want me to write to handle the problem shown; namely, that the continuous set of ranges of 400 total less than the target number of 2000 in my chart but which could just as easily be 20000 as well.

[ayazgreat]

Stop looking at the numbers I am using and look at the structure of the table instead. I could construct a table for 20000, but all that would involve is many more rows of 400 each... I am trying to keep the table small for posting purposes while trying to get you to tell me how to handle the structural problem that occurs when a continuous set of ranges of 400 total less than the target number. Look at my last post, make believe 2000 is a possible target and tell me how you want the program you want me to write to handle the problem shown; namely, that the continuous set of ranges of 400 total less than the target number of 2000 in my chart but which could just as easily be 20000 as well.

Rick Rothstein what do you think ? I do not understand how to handle the structural problem that occurs when a continuous set of ranges of 400 total less than the target number, as i let you know that I receive data in this form (mix up of continuous and non continuous ranges), you think there is no way to handle this knd of data ?