Hot Topics

sorting data in Excel

  hastelloy 08:35 27 Oct 2013

A friend of mine has a spreadsheet in which each record occupies 4 rows and has 2 columns of data followed by a column for each week of the year.

Is it possible to sort the data so that the top row of the 2nd column of each record is in alphabetical order? I have tried to get her to let me put everything in each record on 1 row but she doesn't like that.

  lotvic 17:42 27 Oct 2013

Unless there is some sort of very clever macro you know how to write.... I think it would be simpler if she changed her mind and got to like the 1 row solution you have suggested ;-)

Also a tip she may not be aware of: When sorting do NOT have any blank empty or Hidden columns/rows. It really messes up the sorting. I messed up a sheet like that and now the phone numbers don't match the people - didn't notice until it was too late to undo it :(

  wee eddie 18:58 27 Oct 2013

Best to work on a copy of the spread sheet as I am not sure of this.

Highlight the second column by clicking on the top. Select, Sort

  compumac 19:43 27 Oct 2013

One has to ask as to the purpose of the spreadsheet as it is so straightforward to have all of the information relative to a record on one line. Is there a specific reason for the way being considered?

  lotvic 19:59 27 Oct 2013

"Best to work on a copy of the spread sheet" Oh how I wished I had done that at the time (several years ago), a lesson learnt.

  hastelloy 09:02 28 Oct 2013

Thanks for all the response.

The first 2 columns contain information about people she works with which needs to be visible all the time so, if put into separate columns it would take up 8 columns some of which would be fairly wide. This would take up most of the width and leave little room for the weekly info (1 week per column).

wee eddie

If I do that it sort by the row, not by 4 rows.

I guess my original thought that it couldn't be done is correct .................................................................................................

unless anyone knows different?

  wee eddie 18:07 28 Oct 2013

Of course it can be done but I think that maybe you are asking the wrong question.

I am trying to get a picture of the Spreadsheet in my mind.

Freezing those first 2 Columns might do the trick

  Picklefactory 13:17 29 Oct 2013

I'm with wee eddie. I'm not sure I have a clear understanding of how this is intended to look.

Is this correct?

A 2x4 block of 8 cells each containing various data on a given person and then 52 columns to the right, one for each week of the year and also 4 rows high.

e.g. Starting top left of a new sheet cells A1:B4 containing details, and then cells C1:BB4 for more info to be added each week.

Will consecutive blocks of data be progressing vertically down the sheet? How many rows of the 4x52 weekly columns will be in use?

So effectively each persons data will consist of a solid block of cells 4 rows x 54 columns and they want to sort the whole sheet alphabetically by way of cell B1 (In the instance of the first block) and the matching cell of each subsequent block.

Does that sound anywhere near?

  Graphicool1 13:48 29 Oct 2013

Hi Marvin42 here's a link where you can download FREE manuals for Excel. CLICK HERE

  hastelloy 19:30 29 Oct 2013


You are spot on - there will be 15 to 20 sets of records 4 cells high.


Many thanks for the link. I'll have a good look as soon as I can.

wee eddie

Picklefactory's description is much better than mine. I already have the 1st 2 columns frozen but the trick (if it can be done) is to keep each block of 4 rows together whilst sorting on the top row of each set of 4.

  Picklefactory 23:01 29 Oct 2013

I've had a go at it, but I'm sorry to say it's beyond me, I'm afraid. I think that is really quite a difficult thing to do. I would recommend the MrExcel website for this one, there are many serious experts on there who may be able to help. Sorry I can't be of more/any help.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Huawei P40 Review

Affinity offers Photo, Designer & Publisher for Free for 3 Months

New iPhone SE could launch 'as early as today'

Les meilleures offres gratuites pendant la période de confinement