divide equally

  • Thread starter Thread starter bayanbaru
  • Start date Start date
B

bayanbaru

I have a list of part number (total 1605 items) in column
A. I want to divide them into pages with each page having
5 columns and each column having a count of 50 items.
This will give me 6 pages (5x50x6=1500itesm) with the last
page with 21 items. How do I go about it?
 
bayanbaru,

I assume that your list is in range A1:A1605 on Sheet1 of a workbook.

1. Insert new worksheets so that you have Sheets1 - Sheet8.
Sheet2 - Sheet8 should be empty.

2. In cell G1 of Sheet2 - Sheet8, enter numbers 1, 2, 3, 4, 5, 6, 7
(G1 of Sheet2 should contain 1 ... G1 of Sheet8 should contain 7)

3. Enter this formula in cell A1 of Sheet2:
=OFFSET(Sheet1!$A$1,(ROW()-1)+((COLUMN()-1)*50)+(($G$1-1)*250),0)&""

4. Copy cell A1 of Sheet2 and paste it into the range A1:E50 of Sheet2

5 Copy the range A1:E50 of Sheet2 and paste it into A1:A50 of Sheet3 -
Sheet8

6. Finally, you may wish to convert the formulas to values.
Repeat these steps for Sheet2 - Sheet8:
6.1 Select A1:A50
6.2 Edit - Copy
6.3 Edit - Paste Special - Select "Values" - OK
6.4 Select cell G1 and press Delete

Ture Magnusson
Karlstad, Sweden
 
Thanks It does help me alot.
-----Original Message-----
bayanbaru,

I assume that your list is in range A1:A1605 on Sheet1 of a workbook.

1. Insert new worksheets so that you have Sheets1 - Sheet8.
Sheet2 - Sheet8 should be empty.

2. In cell G1 of Sheet2 - Sheet8, enter numbers 1, 2, 3, 4, 5, 6, 7
(G1 of Sheet2 should contain 1 ... G1 of Sheet8 should contain 7)

3. Enter this formula in cell A1 of Sheet2:
=OFFSET(Sheet1!$A$1,(ROW()-1)+((COLUMN()-1)*50)+ (($G$1-1)*250),0)&""

4. Copy cell A1 of Sheet2 and paste it into the range A1:E50 of Sheet2

5 Copy the range A1:E50 of Sheet2 and paste it into A1:A50 of Sheet3 -
Sheet8

6. Finally, you may wish to convert the formulas to values.
Repeat these steps for Sheet2 - Sheet8:
6.1 Select A1:A50
6.2 Edit - Copy
6.3 Edit - Paste Special - Select "Values" - OK
6.4 Select cell G1 and press Delete

Ture Magnusson
Karlstad, Sweden




.
Click to expand...
 
Back
Top