making column break to new column??

JLAudioFan

New Member
Joined
Nov 11, 2005
Messages
2
First I'd like to say... WOW!! There is so much useful info here! Problem is, I am not really sure how to ask this question (or how to search for it) so i'll try and explain what to do.

I have a list of PLU codes, which has 3 columns per row (PLU, DESCRIPTION, PRICE). This group of 3 columns does not take up alot of room on a page (especially in landscape format). What i'd like to do is get excel to break up the rows so that I have 2 or 3 columns of this info on one page. I'm trying to make a list that is easy and quick to look through for the cashiers. it'd look like this...

i'd like :

PLU DESC PRICE PLU DESC PRICE
PLU DESC PRICE PLU DESC PRICE
PLU DESC PRICE PLU DESC PRICE
PLU DESC PRICE PLU DESC PRICE

instead of

PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE
PLU DESC PRICE


Yes I can cut/paste but I don't have time to do this every time I add one product to this list.

Your help is GREATLY appreciated!!!


Jason
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
JLAudioFan

Welcome to the Mr Excel board!

I think this is more like what you are after.

1.Set up headings and numbers in what you think might be the right sort of arrangement considering the size and type of data and what will fit on your page in terms of width, like my green cells below.

2. Now in cell F3 put the formula:
=OFFSET($A$1,MAX($E:$E)*INT((F$1-1)/3)+$E3,MOD(F$1-1,3))

3. Drag this formula across all the columns in your grid and then down all the rows.

4. Set the Print Area to F2:N6

If you don't like the zeros appearing at the end, you could just delete them, or use Conditional Formatting to make them white, or we could modify the formulas to not put the zeros in in the first place. However, let's see if this is the sort of thing you were after.

If the 'shape' of the grid does not turn out right for printing, it is fairly easy to change the structure by altering the green cell layout and drag the formula again.
Mr Excel.xls
ABCDEFGHIJKLMN
1PLUDESCPRICE123456789
2Plu1Desc11.00PLUDESCPRICEPLUDESCPRICEPLUDESCPRICE
3Plu2Desc21.501Plu1Desc11.00Plu5Desc53.00Plu9Desc95.00
4Plu3Desc32.002Plu2Desc21.50Plu6Desc63.50Plu10Desc105.50
5Plu4Desc42.503Plu3Desc32.00Plu7Desc74.00000.00
6Plu5Desc53.004Plu4Desc42.50Plu8Desc84.50000.00
7Plu6Desc63.50
8Plu7Desc74.00
9Plu8Desc84.50
10Plu9Desc95.00
11Plu10Desc105.50
Convert List to Grid
 
Upvote 0
Thanks for your help Peter!

I got the formula to go into cell F3, but I get lost after that. I tried to select the columns/rows and put the formula in there, but I don't think that i'm doing this right. I'll try to copy the formula from your table into each cell to see if that works.

Here's a sample of my info:

PLU Description Price
8712 Alfredo Sauce 0
3064 Aloe Vera Leaves 0
4677 Anaheim Chile 1.59
4515 Anise 0.99
8507 Argentina Lrg 9.99
8508 Argentina Sml 13.99
4084 Artichokes 1.29
4521 Asparagus 2.99
8708 Baked Liti 9.99
4409 Bartlett Pears 0.89
8715 Basil Pesto 7.99
4539 Beet Bunch w/top 1.49


I haven't figured out how to show you the actual spreadsheet yet, ill see if i can get that to work. Thanks again for your help.

Jason
 
Upvote 0
JLAudioFan said:
Thanks for your help Peter!

I got the formula to go into cell F3, but I get lost after that. I tried to select the columns/rows and put the formula in there, but I don't think that i'm doing this right. I'll try to copy the formula from your table into each cell to see if that works.

...

I haven't figured out how to show you the actual spreadsheet yet, ill see if i can get that to work. Thanks again for your help.

Jason
'Dragging' the formula:
1. Get the formula into F3 (you have done that)
2. Select F3
3. The 'Fill Handle' is the little black square at the bottom right of the cell. Click and drag the Fill Handle across until you get to N3 (in my example at least) then let go.
4. Now you should have F3:N3 selected with the Fill Handle on cell N3.
5. Click and drag the Fill Handle down to the bottom right cell (N6 in my example) and let go.
There you have it, formula copied to all cells in the grid.

Displaying your sheet on the board:
To do so, see the link at the bottom of this page to Colo's HTML Maker. Also read the ‘Sticky’ titled: 'HTML Maker FAQ (How to show your sheet on the board)' which is at the top of this ‘Excel Questions’ forum. Test it out in the ‘Test Here’ forum which is found in the ‘MrExcel Message Board Forum Index’ before trying to use it in this forum.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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