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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

izzy

Board Regular
Joined
Jun 12, 2003
Messages
165
Increase your page margins and preview print until you get it just right.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

JLAudioFan

New Member
Joined
Nov 11, 2005
Messages
2
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

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
Top