Layout/Sorting of Spreadsheet

[Karen615]

Using Excel 2007
I have a spreadsheet that has a list of ~ 300 customers that I want to keep in alphabetical order.
Currently the list is split up into 3 columns on 2 pages with A-M on page one and N-Z on page two. The problem with this is that, when I have to add a new customer, I have to shift everything around. Is there a way to have all the customers in column A so it's easier to add a new customer and then be able to sort and then have it print on 2 pages and 3 columns? Not sure the best way to do this.
Your help is GREATLY appreciated.
Karen

[Pete_UK]

Take a look at this thread from a few days ago:

http://www.excelforum.com/excel-form...html?p=2956449

It shows how you can take those names in column A and put them into 3 columns, so, Yes, you could put them all in one sheet.

Hope this helps.

Pete

[RHCPgergo]

Hi,

I think you should copy all the data to one worksheet. It make your spreadsheet way more effective.

[Karen615]

Hi,

I think you should copy all the data to one worksheet. It make your spreadsheet way more effective.

Thank you for your reply. The data is in one worksheet.
Thanks, Karen

[Karen615]

Take a look at this thread from a few days ago:

Thank you very much! I'll check this out a little later and see if it works for me.
Thank you again!
Karen

[Karen615]

Thanks Pete!

This is an excellent solution! Although, how would I modify the formula if I have the headings in:
A2: Customer
B2: Discount
C2: Amount

The first cells of data in:
A3, B3, C3

I would want the 3 columns of data to be ABC, EFG, IJK

Thanks again for your help!

[Pete_UK]

Hi Karen,

I've set this up in the attached workbook so that you can just copy your data into columns A, B and C, and then just copy the formulae in columns E to O down as far as you need them (until you get zeros.

Hope this helps.

Pete

[Karen615]

Pete,

This is great! Thank you so much for doing this for me. You have helped me tremendously!

Is it best to hide columns A:C?
If you have time, it's not necessary since you were so kind to set this up for me, but would you explain the formula in layman's terms?

Thank you again Pete!

Karen

[Pete_UK]

I thought columns A to C were your actual data, where you might add names and re-sort the list, so you don't want to hide them. Maybe it would be better to have the names and raw-data in one sheet and the transposed data in another sheet.

I'm just about to go to bed here, so I'll have to pick this up (and your other request for an explanation) tomorrow. If your actual data differs from what I have set up in columns A to C, then please post a more representative Excel file (not a picture) so I can set it up in that.

Pete

[Karen615]

Pete,
Thank you again for taking the time to do this for me. It's very sweet of you!
It will probably be better if I have the raw data on Sheet 1 and the transposed data on Sheet 2. It will be easier to update.
Is there any way to have the data in order from top to bottom instead of left to right? If not, this is fine. If it is top to bottom, how would that work when it spans to 2 pages?
Karen

[Pete_UK]

That's the problem with going down - how many records to allow for the page. It is a lot easier to transpose the data going across in groups of three.

I'll have a look at splitting it into 2 separate sheets, as I suggested.

Pete

[Pete_UK]

Ah well, that wasn't too difficult - just cut and paste, with a bit of tidying up (see attached file). You can now just copy your actual data into the Main_data sheet, and in the Transposed sheet you can copy the formulae down as far as you need to.

As for how it works, the formulae all make use of the ROWS and COLUMNS functions, as well as MOD and INT. As the formulae are copied down we basically want to obtain data from the rows in the main sheet like this:

A3 B3 C3 ... A4 B4 C4 ... A5 B5 C5
A6 B6 C6 ... A7 B7 C7 ...

and so on. The formulae ensure that the appropriate numbers are returned for the cell that the formula is in such that it brings the correct data from the main sheet in the correct order by means of the INDEX function. You can strip away the index function just the see the numbers.

(I know that's not a detailed explanation, but hopefully it makes a bit of sense late at night).

Hope this helps.

Pete

[Karen615]

Pete,
Thank you again for all your help! This works great!
Your help is greatly appreciated!!
Karen

[Pete_UK]

Please mark the thread as Solved if you now consider it to be so - the FAQ describes how.

Also, you can pass your thanks on directly to contributors by clicking on the "star" icon in the bottom left corner of any post you have found to be helpful.

Pete