Start & End Number Further Converted

[ayazgreat]

Hi

I need you help regarding to attached file example data , there are different start and end numbers with each qty equal to 400 , I want them to be further converted into Starat and End Range as mentioned in example attached sheet.

Thanks in advance

[Excel Fox]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?

[ayazgreat]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?

Because data is sorted and in ascending order you see the same in second example start and end range data is matched a and Qty of each range is qual to first example

[Rick Rothstein]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?
I think the question Excel Fox wants to know is... will the second set always be totalling to 100000 or less (you don't say that in its header)?

I have a question of my own for you... Is the 400 shown down Column C always 400 in every cell or will those numbers vary down the column in "real life"?

[ayazgreat]

I think the question Excel Fox wants to know is... will the second set always be totalling to 100000 or less (you don't say that in its header)?

I have a question of my own for you... Is the 400 shown down Column C always 400 in every cell or will those numbers vary down the column in "real life"?

Thanks for your reply, second set can not always be totalling to 100000, it might be less
And column c is always 400, you can say Qty or count is always equal to 400 in Column c

[Rick Rothstein]

Thanks for your reply, second set can not always be totalling to 100000, it might be less

Okay, if that is the case, then how do we know what number to total up to in order to determine the ranges? Is the number being stored in a cell somewhere (if so, where)?

[ayazgreat]

Okay, if that is the case, then how do we know what number to total up to in order to determine the ranges? Is the number being stored in a cell somewhere (if so, where)?

All numbers are stored from column a to b with their Qty in column c and this the query to be developed in code to to determine start range and end range of the same number as I mentioned in attached sheet in two different examples.

[Rick Rothstein]

Give the following macro a try and see if you can make use of it...


Sub StartEndRanges()
Dim X As Long, TargetNumber As Long, NumberOfRows As Long, LastRow As Long, Increment As Long, DestinationStartCell As Range
Const FixedQty As Long = 400
Const StartRow As Long = 2
TargetNumber = Application.InputBox("What quantity total do you want ranges for (note... must be a multiple of 400)?", Type:=1)
If TargetNumber = 0 Or TargetNumber Mod 400 <> 0 Then
MsgBox "The number """ & TargetNumber & """ is not valid!", vbExclamation
End If
On Error GoTo NoCell
Set DestinationStartCell = Application.InputBox("Please select the start cell for output?", Type:=8)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
NumberOfRows = TargetNumber \ FixedQty
With DestinationStartCell
.Resize(, 3).Merge
.Value = "Result After Macro: " & TargetNumber & " or less"
.HorizontalAlignment = xlHAlignCenter
.Interior.ColorIndex = 48
.Font.Bold = True
With .Offset(1).Resize(, 3)
.Value = Array("Start Range", "End Range", "Qty")
.Interior.ColorIndex = 15
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True
.ColumnWidth = 15
End With
End With
For X = StartRow To LastRow Step NumberOfRows
DestinationStartCell.Offset(2 + Increment).Value = Cells(X, "A").Value
DestinationStartCell.Offset(2 + Increment).Offset(, 1).Value = Cells(X + NumberOfRows - 1, "B").Value
DestinationStartCell.Offset(2 + Increment).Offset(, 2).Value = WorksheetFunction.Sum(Cells(X, "C").Resize(NumberOfRows))
Increment = Increment + 1
Next
DestinationStartCell.Offset(1 + Increment).Offset(, 1).Value = Cells(LastRow, "B").Value
NoCell:
End Sub
Note: The code will ask you for the quantity total you want the list broken down by (you would 20000 for your first chart, 100000 for your second chart) and the starting cell (top left corner) to output the chart to (which does not have to be on Row 1 if you do not want it to be).

[ayazgreat]

Thank you very Rick Rothstein
It works really great , more than my expectation.

[Rick Rothstein]

Thank you very Rick Rothstein
It works really great , more than my expectation.
You are quite welcome... I am glad I was able to be of help to you.

[ayazgreat]

Rick Rothstein
I want to ask something more regarding above post that if Column a & b Start & End number but Column C Qty of each range is 20000 and I want to convert each range from col a to c same into 400 as in previouse code we have qty in col c and we can convert them to 20000 or more but Now case is oposite it.

Thanks in advance

[Excel Fox]

Are you saying that you now want to generate the input from the output?

[ayazgreat]

Yes Kris, you are absolultly right now I want each 20000 range (col a to c) to be converted into 400 range of different series.

[Rick Rothstein]

Yes Kris, you are absolultly right now I want each 20000 range (col a to c) to be converted into 400 range of different series.
Okay, give the following macro a try. You will be asked 3 questions. The first question asks you to select the cell with the first "Start Range" number in it. In your example case, that would be cell A2 or cell F3 or cell J3 depending on which chart you are going to reference. The second question asks you to input the quantity to break the chart out by (same as asked for in my original code). The third question asks you to select the first cell to start the chart at (again, same as asked for in my original code).


Sub StartEndRanges()
Dim X As Long, TargetQty As Long, StartRow As Long, LastRow As Long, RangeStart As Long, RangeEnd As Long
Dim TotalQty As Double, NumberOfFullRows As Long, StartRangeCell As Range, DestinationStartCell As Range
On Error GoTo NoCell
Set StartRangeCell = Application.InputBox("Select 1st Start Range cell in table to convert from.", Type:=8)
StartRow = StartRangeCell.Row
LastRow = StartRangeCell.End(xlDown).Row
RangeStart = StartRangeCell.Value
RangeEnd = StartRangeCell.Offset(LastRow - StartRow, 1).Value
TotalQty = WorksheetFunction.Sum(StartRangeCell.Offset(, 2).Resize(LastRow - StartRow + 1))
TargetQty = Application.InputBox("What quantity do you want for each ranges", Type:=1)
If TargetQty <= 0 Or TargetQty Like "*[!0-9]*" Then
MsgBox "The number """ & TargetQty & """ is not valid!", vbExclamation
Exit Sub
End If
Set DestinationStartCell = Application.InputBox("Please select the start cell for output?", Type:=8)
On Error GoTo 0
NumberOfFullRows = TotalQty \ TargetQty
With DestinationStartCell
.Resize(, 3).Merge
.Value = "Result After Macro: " & TargetQty & " or less"
.HorizontalAlignment = xlHAlignCenter
.Interior.ColorIndex = 48
.Font.Bold = True
With .Offset(1).Resize(, 3)
.Value = Array("Start Range", "End Range", "Qty")
.Interior.ColorIndex = 15
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True
.ColumnWidth = 15
End With
.Resize(NumberOfFullRows).Offset(2, 2).Value = TargetQty
If TotalQty - NumberOfFullRows * TargetQty Then
Cells(.Row + NumberOfFullRows + 2, .Column + 2).Value = TotalQty - NumberOfFullRows * TargetQty
End If
For X = 0 To NumberOfFullRows + (TotalQty = NumberOfFullRows * TargetQty)
Cells(.Row + X + 2, .Column).Value = RangeStart + X * TargetQty
Cells(.Row + X + 2, .Column + 1).Value = Cells(.Row + X + 2, .Column).Value + _
Cells(.Row + X + 2, .Column + 2).Value - 1
Next
If TotalQty > NumberOfFullRows * TargetQty Then
With Cells(.Row + NumberOfFullRows + 2, .Column + 1)
.Value = .Value + Cells(.Row + NumberOfFullRows + 2, .Column + 2).Value
End With
End If
End With
NoCell:
End Sub

[ayazgreat]

Dear Rick Rothstein

The macro is giving error , I am attaching here a workbook with expected result.

[Rick Rothstein]

Dear Rick Rothstein

The macro is giving error , I am attaching here a workbook with expected result.



A

B

C



1

Start Range

End Range

Qty



2

1000394600

1000414599

20000



3

1000825600

1000845599

20000



4

1002832000

1002851999

20000




In the above snapshot from your worksheet, the two highlighted ranges overlap... is that correct? I have assumed (even in my first posted code) that the ranges link together one-to-the-other without an breaks in the ranges. Are you telling us that the ranges do not have to be contiguous throughout the table? If so, then I do not think my original code actually works for your first posted question. Please let us know about your ranges and whether they can be non-contiguous or overlap.

[ayazgreat]

Dear Rick Rothstein
In my all posts from starting to end all ranges data provided by me in breaks , in both posts case ranges do not have contiguous throughout the table.They can be non-contiguous or overlap

[Rick Rothstein]

In my all posts from starting to end all ranges data provided by me in breaks , in both posts case ranges do not have contiguous throughout the table.They can be non-contiguous or overlap
Sorry about the delay in getting back to you... I had some personal stuff to take care of.

Okay, I think I am confused. Your first posting showed ranges that were continuous, without breaks or overlaps, and you indicated the code I gave you worked fine for you. Now you are showing ranges with breaks and overlaps... is this a separate question, using different source data than than you showed us in your first message. If not, that is, if your data for the first question can also have breaks and overlaps, then the function I posted (which you said works for you) does not really work correctly. If you are now asking for help with a similar looking data structure, but one that is different from the first in that it can have breaks and overlaps in its ranges, then I need to know this. The problem I have right now is in figuring out if I have to fix my first function or not. Can you please clarify all of this for me?

[ayazgreat]

Sorry about the delay in getting back to you... I had some personal stuff to take care of.

Okay, I think I am confused. Your first posting showed ranges that were continuous, without breaks or overlaps, and you indicated the code I gave you worked fine for you. Now you are showing ranges with breaks and overlaps... is this a separate question, using different source data than than you showed us in your first message. If not, that is, if your data for the first question can also have breaks and overlaps, then the function I posted (which you said works for you) does not really work correctly. If you are now asking for help with a similar looking data structure, but one that is different from the first in that it can have breaks and overlaps in its ranges, then I need to know this. The problem I have right now is in figuring out if I have to fix my first function or not. Can you please clarify all of this for me?

Hi Rick
How are you ? Have a good day.
As I have said before that in my first post ranges are in breaks , you can see my first attached sheet ranges and you will find there ranges in breaks, and come to your 2nd question about having result after running your code , you are right in saying that your code doesn't do right work on break ranges, that was time I did not see result carefully but seen when you mentioned about it.
Now could It b possible to correction in both result?

Thanks in advance
Ayaz

[Rick Rothstein]

Hi Rick
How are you ? Have a good day.
As I have said before that in my first post ranges are in breaks , you can see my first attached sheet ranges and you will find there ranges in breaks, and come to your 2nd question about having result after running your code , you are right in saying that your code doesn't do right work on break ranges, that was time I did not see result carefully but seen when you mentioned about it.
Now could It b possible to correction in both result?

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.

[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 ?

[ayazgreat]

So excel gurus you think that there is no solution for query I asked

[Excel Fox]

Ayaz, if you look at what Rick is implying, there has to be a logic to the way your data is laid out for it to be logically split and/or combined. If that is not consistent, it would be difficult for a logic to be developed around it. If there aren't enough records to be grouped to form the target sum, how do you want that to be handled? If you can get that across, then only can the solution be aptly developed

[ayazgreat]

As I have replied before that I receive daya in this form so how should I change or make further development in the same?