Need help creating a macro working with a range of numbers

Status
Not open for further replies.
R

rostar99

New Member
Joined
Apr 8, 2013
Messages
5
I need to split up records that have greater than 2000 packages.
Sheet1-Data
Excel 2007
ABCDEFGHIJKL
order numberCopy numberPlan NumberPlan NameStock Code 1Stock Code 2Stock Code 3Stock Code 4
O'TOOLS AUTO, INC.UPDATE Z
1034815-1034816

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #DBEEF3"]Total Packages[/TD]
[TD="bgcolor: #DBEEF3"]Total Sheets[/TD]
[TD="bgcolor: #DBEEF3"]Total Images[/TD]
[TD="bgcolor: #FFFF00"]Control Number Range[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]34814[/TD]
[TD="bgcolor: #DBEEF3, align: right"]69799[/TD]
[TD="bgcolor: #DBEEF3, align: right"]139598[/TD]
[TD="bgcolor: #FFFF00"]1000001-1034814[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]154730[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



The control number range in "Sheet1" (highlighted in yellow) represents the numbers of the individual packages for each record . The first record has 34814 packages and a control number range of 1000001-1034814. As this is greater than 2000 it must be broken up into ranges of 2000 ( start 1000001 end 1002000, start 1002001 end 1004000, etc). The Total Package, Total Sheets, and Total Images are also split up (highlighted in blue). If control number range and packages is equal to or less than 2000 then that row gets copied over to the other sheet with no additional rows and the the control number range number just gets split into a start and end control number (1034815-1034816 changes to start 1034815 end 1034816). The batch number is simply the first digit of the control number "1" combined with the row number(-01, -02 etc.).

Result
Excel 2007
ABCDEFGHIJKLMN
Order numberCopy numberBatch NumberPlan NumberPlan NameStock Code 1Stock Code 2Stock Code 3Stock Code 4
1-01O'TOOLS AUTO, INC.UPDATE Z
1-02O'TOOLS AUTO, INC.UPDATE Z
1-03O'TOOLS AUTO, INC.UPDATE Z
1-04O'TOOLS AUTO, INC.UPDATE Z
1-05O'TOOLS AUTO, INC.UPDATE Z
1-06O'TOOLS AUTO, INC.UPDATE Z
1-07O'TOOLS AUTO, INC.UPDATE Z
1-08O'TOOLS AUTO, INC.UPDATE Z
1-09O'TOOLS AUTO, INC.UPDATE Z
1-10O'TOOLS AUTO, INC.UPDATE Z
1-11O'TOOLS AUTO, INC.UPDATE Z
1-12O'TOOLS AUTO, INC.UPDATE Z
1-13O'TOOLS AUTO, INC.UPDATE Z
1-14O'TOOLS AUTO, INC.UPDATE Z
1-15O'TOOLS AUTO, INC.UPDATE Z
1-16O'TOOLS AUTO, INC.UPDATE Z
1-17O'TOOLS AUTO, INC.UPDATE Z
1-18O'TOOLS AUTO, INC.UPDATE Z
1-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #DBEEF3"]Total Packages[/TD]
[TD="bgcolor: #DBEEF3"]Total Sheets[/TD]
[TD="bgcolor: #DBEEF3"]Total Images[/TD]
[TD="bgcolor: #FFFF00"]Control NumberStart[/TD]
[TD="bgcolor: #FFFF00"]Control NumberEnd[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1000001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1002000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1002001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1004000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1004001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1006000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1006001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1008000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1008001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1010000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1010001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1012000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1012001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1014000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1014001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1016000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1016001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1018000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1018001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1020000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1020001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1022000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1022001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1024000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1024001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1026000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1026001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1028000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1028001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1030000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1030001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1032000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]2000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]4000[/TD]
[TD="bgcolor: #DBEEF3, align: right"]8000[/TD]
[TD="bgcolor: #FFFF00, align: right"]1032001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1034000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="bgcolor: #DBEEF3, align: right"]814[/TD]
[TD="bgcolor: #DBEEF3, align: right"]1799[/TD]
[TD="bgcolor: #DBEEF3, align: right"]3598[/TD]
[TD="bgcolor: #FFFF00, align: right"]1034001[/TD]
[TD="bgcolor: #FFFF00, align: right"]1034814[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]154730[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1034815[/TD]
[TD="align: right"]1034816[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Result​



I have over 75,000 records to manipulate so any help would be appreciated.

Thank you,

Bob
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
B
  • Question Question
Need help creating a second ListBoxOutput without corrupting the first.
Replies
3
Views
421
Blasirl
B
Oberon70
  • Question Question
Ideas to help modified code to work with different conditions
Replies
0
Views
431
Oberon70
Oberon70
C
  • Question Question
Need help create macro to auto populate data from cell on an employee sheet to a master sheet by first referencing store number
Replies
3
Views
3K
mrshl9898
M
J
  • Question Question
Help with Formula's please and thank you
Replies
1
Views
350
XLfriend777
XLfriend777

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top