Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 8th 13, 03:25 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default Sorting rows

I have my recorded music collection catalogued in a worksheet where column A shows the date when a new addition to my collection is added, column B shows the sequential number identifying the recording, column C shows the name of the composer. Other columns show the names of the orchestra, performers etc. I would like to sort this worksheet in alphabetical order of composer in such a way that the whole row moves i.e the sheet would appear the same after sorting; apart from the catalogue numbers in column B which would no longer be sequential.
I would normally maintain the worksheet in this composer sorted condition but I will need to be able to revert to the original condition when I want to add a further recording and need to locate and allocate the next sequential number.

Can anyone tell me if and how this can be achieved?

  #2   Report Post  
Old September 8th 13, 03:56 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Sun, 8 Sep 2013 15:25:53 +0100 schrieb Quilp:

I have my recorded music collection catalogued in a worksheet where
column A shows the date when a new addition to my collection is added,
column B shows the sequential number identifying the recording, column C
shows the name of the composer. Other columns show the names of the
orchestra, performers etc. I would like to sort this worksheet in
alphabetical order of composer in such a way that the whole row moves
i.e the sheet would appear the same after sorting; apart from the
catalogue numbers in column B which would no longer be sequential.
I would normally maintain the worksheet in this composer sorted
condition but I will need to be able to revert to the original condition
when I want to add a further recording and need to locate and allocate
the next sequential number.


select one cell in your table = Data = Sort.
In the new dialog choose Sort by column C


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Old September 9th 13, 04:11 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Sun, 8 Sep 2013 15:25:53 +0100 schrieb Quilp:

I have my recorded music collection catalogued in a worksheet where
column A shows the date when a new addition to my collection is added,
column B shows the sequential number identifying the recording, column C
shows the name of the composer. Other columns show the names of the
orchestra, performers etc. I would like to sort this worksheet in
alphabetical order of composer in such a way that the whole row moves
i.e the sheet would appear the same after sorting; apart from the
catalogue numbers in column B which would no longer be sequential.
I would normally maintain the worksheet in this composer sorted
condition but I will need to be able to revert to the original condition
when I want to add a further recording and need to locate and allocate
the next sequential number.


select one cell in your table = Data = Sort.
In the new dialog choose Sort by column C


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Thanks Claus - easy when you know how!
  #4   Report Post  
Old September 11th 13, 07:12 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Having sorted part of my worksheet, thanks to Claus, I am now wondering if it is possible to individually sort sections of the sheet.
The single page sheet shows the sequential catalogue number in three groups and I have sorted the cd's section O.K. but I would now like to sort the Digital and DVD sections independently of each other and of the cd section. Is this possible or will I have to split the sheet into three pages and independently sort each page?

To clarify, I have allocated numbers 1-505 to cd's, then used the following two rows to enter a heading "Digital Music" and then started using rows 507 to 571 to list my mp3 downloads followed by a heading "DVD's" in rows 572-3 after which I have listed my DVD collection.

Last edited by Quilp : September 11th 13 at 07:27 PM Reason: Tried to attach my worksheet but failed
  #5   Report Post  
Old September 11th 13, 07:57 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Wed, 11 Sep 2013 19:12:21 +0100 schrieb Quilp:

To clarify, I have allocated numbers 1-505 to cd's, then used the
following two rows to enter a heading "Digital Music" and then started
using rows 507 to 571 to list my mp3 downloads followed by a heading
"DVD's" in rows 572-3 after which I have listed my DVD collection.


insert 2 empty rows between the different sections. Then you can sort
the three parts in three actions


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


  #6   Report Post  
Old September 12th 13, 03:31 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

Am Wed, 11 Sep 2013 19:12:21 +0100 schrieb Quilp:

To clarify, I have allocated numbers 1-505 to cd's, then used the
following two rows to enter a heading "Digital Music" and then started
using rows 507 to 571 to list my mp3 downloads followed by a heading
"DVD's" in rows 572-3 after which I have listed my DVD collection.


insert 2 empty rows between the different sections. Then you can sort
the three parts in three actions


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Thank you Claus - your help to this old newbie is much appreciated.
  #7   Report Post  
Old September 12th 13, 03:57 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

Quote:
Originally Posted by Quilp View Post
Thank you Claus - your help to this old newbie is much appreciated.
I now have a further question - rather than having to reverse the sortings, when I need to find the last number allocated in order to use the next one for a new recording entry, is it possible to find this number by using the "find" feature or in some other way?
  #8   Report Post  
Old September 12th 13, 04:58 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Thu, 12 Sep 2013 15:57:52 +0100 schrieb Quilp:

I now have a further question - rather than having to reverse the
sortings, when I need to find the last number allocated in order to use
the next one for a new recording entry, is it possible to find this
number by using the "find" feature or in some other way?


I don't really understand what number you are looking for.
If you have sorted by composer the numbers are not in order.
The last number in column B you get with:
=LOOKUP(2,1/(B1:B1000),B:B)
But the highest number you get with:
=MAX(B:B)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #9   Report Post  
Old September 13th 13, 10:16 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2013
Posts: 27
Default

[quote=Claus Busch;1613831]Hi,

Am Thu, 12 Sep 2013 15:57:52 +0100 schrieb Quilp:

I now have a further question - rather than having to reverse the
sortings, when I need to find the last number allocated in order to use
the next one for a new recording entry, is it possible to find this
number by using the "find" feature or in some other way?


I don't really understand what number you are looking for.
If you have sorted by composer the numbers are not in order.
The last number in column B you get with:
=LOOKUP(2,1/(B1:B1000),B:B)
But the highest number you get with:
=MAX(B:B)

Yes - the numbers are not in order which is the problem when I need to find the highest number so that I can allocate the next one to a newly entered recording.
=LOOKUP(2,1/(B1:B1000),B:B) returns REF
=MAX(B:B) returns 0

I have now made it possible to find quickly the highest number in my cataloguing sequence by inserting an extra column, now column A, and entering sequential numbers into it. However, this seems a clumsy way to do it.
  #10   Report Post  
Old September 13th 13, 10:55 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,607
Default Sorting rows

Hi,

Am Fri, 13 Sep 2013 10:16:11 +0100 schrieb Quilp:

Yes - the numbers are not in order which is the problem when I need to
find the highest number so that I can allocate the next one to a newly
entered recording.
=LOOKUP(2,1/(B1:B1000),B:B) returns REF
=MAX(B:B) returns 0


in which column are your numbers? You have to modify the references to
your sheet.

I have now made it possible to find quickly the highest number in my
cataloguing sequence by inserting an extra column, now column A, and
entering sequential numbers into it. However, this seems a clumsy way
to do it.


Another way is to insert a new column with your medium, e.g. CD, DVD.
Then you can put your tables together to one table, you can sort first
by medium and then by composer and if you want enter new data you can
sort by number, and so on.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting rows out of order after all rows are numbered in sequence Mikey Excel Programming 1 December 16th 09 10:41 PM
sorting rows Mike Excel Discussion (Misc queries) 1 November 7th 09 07:54 PM
Sorting block of rows with empty rows between it jgmiddel[_9_] Excel Programming 1 October 2nd 08 01:38 PM
Sorting rows Lowell Excel Programming 4 May 13th 07 05:54 AM
[sorting rows] digisummo Excel Worksheet Functions 1 June 16th 06 03:45 PM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017