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.