Printable View
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
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 exampleQuote:
Originally Posted by Excel Fox
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)?Quote:
Originally Posted by Excel Fox
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 lessQuote:
Originally Posted by Rick Rothstein
And column c is always 400, you can say Qty or count is always equal to 400 in Column c
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)?Quote:
Originally Posted by ayazgreat
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.Quote:
Originally Posted by Rick Rothstein
Give the following macro a try and see if you can make use of it...
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).Code: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
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.Quote:
Originally Posted by 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
Are you saying that you now want to generate the input from the output?
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).Quote:
Originally Posted by ayazgreat
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
Dear Rick Rothstein
The macro is giving error , I am attaching here a workbook with expected result.
Quote:
Originally Posted by ayazgreat
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.
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
Sorry about the delay in getting back to you... I had some personal stuff to take care of.Quote:
Originally Posted by ayazgreat
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 RickQuote:
Originally Posted by Rick Rothstein
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
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.Quote:
Originally Posted by ayazgreat
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...Quote:
Originally Posted by Rick Rothstein
I J K 1 Start
RangeEnd
RangeQty 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
RangeEnd
RangeQty 2 10000 2000 3 2000 4 16199 1200
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.
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.Quote:
Originally Posted by Rick Rothstein
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.
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...Quote:
Originally Posted by ayazgreat
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?
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?Quote:
Originally Posted by ayazgreat
A B C D E F G 1 Start Range End Range Qty Result: Target = 2000 or
less2 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
less2 Start Range End Range Qty 3 925874000 925875999 2000 4 925876000
811876400925876799
8118767992000 5 811877600 811878799 1200
What do you think of this idea?
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.Quote:
Originally Posted by ayazgreat
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 ?Quote:
Originally Posted by Rick Rothstein
So excel gurus you think that there is no solution for query I asked
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
As I have replied before that I receive daya in this form so how should I change or make further development in the same?