Hot Topics

sorting data in Excel

  hastelloy 08:35 27 Oct 2013
Locked
Answered

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.

  wee eddie 07:18 30 Oct 2013

It strikes me that much of this information could be hidden and only displayed when needed. Kept hidden in the frozen section

  hastelloy 07:49 30 Oct 2013

Picklefactory

Many thanks - I'll keep looking but I'm not hopeful.

wee eddie

How do you freeze rows, 1,5,9,13,17 etc?

  onthelimit1 08:19 30 Oct 2013

I winder where VoG is now - he would know!

  Simsy 08:53 30 Oct 2013

What you could do is...

Have a 4th column... (which can subsequently be hidden is wished) I'm going to suppose that the top left of each record starts at A1

In cell D1 put the formula; =B1 in Cell D2 put the formula; =B1 In cell D3 put the formula; =B1 in Cell D4 put the formula; =B1

In cell D5 put the formula; =B5 in Cell D6 put the formula; =B5 In cell D7 put the formula; =B5 in Cell D8 put the formula; =B5

Then select the range D1-D8 and copy it down as far as necessary.

Then you can sort by column D.

Depending on the version of Excel you may/may not have to select the whole range to sort. In 2010 version, (which I'm using), if just selecting a single column, when sorting, you get prompted to include adjacent cols with data.

Does this help?

Regards,

Simsy

  Simsy 08:57 30 Oct 2013

Also...

Something like this could be achieved with a macro, but I'm rather out of practice with writing those.

And a slight generic warning about macros...

Generally speaking, if something is performed with a macro it can't be undone! If something goes wrong with a "sort" done with a macro this could be rather messy to put right!

Regards,

Simsy

  hastelloy 15:44 30 Oct 2013

Simsy

I think the suggestion in your first post should work - if it does, it's brilliant. I'll try it and let you know.

  hastelloy 15:44 30 Oct 2013

onthelimit1

I've been having the same thought!

  wee eddie 18:29 30 Oct 2013

It seems to me that the information, like Topsy, has growed and growed.

It might prove simpler to redesign the spread sheet. So that the left hand columns show Name and Forenames. The following columns contain information that can be hidden and then the weekly information in the next 52 columns

  hastelloy 07:53 31 Oct 2013

wee eddie

Thanks for the thought but I already tried that and it becomes unwieldy because the number of frozen columns takes up too much space.

Simsy's suggestions is basically very simple and I believe it should work. I'm hoping to have the time to try it when I see my friend tomorrow morning. If it does work, I'll mark this thread as resolved.

  wee eddie 19:50 31 Oct 2013

You can only sort on the base of a single column, or row, of cells. Of course, other cells will be associated with that cell.

Are you sure that 'Sort' is what you mean

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

Elsewhere on IDG sites

Will 5G kill you?

Becoming an In-House Illustrator: advice from those who've made it

iPhone SE 2 release date, price & specs rumours

Les meilleurs VPN pour Amazon Fire TV Stick (2020)