Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
Quote:
|
#7
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
|
|||
|
|||
[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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#11
|
|||
|
|||
Quote:
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting rows
Hi,
Am Fri, 13 Sep 2013 14:04:47 +0100 schrieb Quilp: The numbers, now out of sequence, are in column B if the name of the CD or DVD is in column E and you will look for the highest sequential number of the name in E1, then try: =MAX(IF(E1:E1000=E1,B1:B1000)) and enter the array formula with CTRL+Shift+Enter. If i still misunderstand your problem please upload a sample. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#13
|
|||
|
|||
Regards
Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2[/quote] I can't see a file format listed in the "Attach Files" which allows me to attach my worksheet. I apologise if my posts are confusing as a result of inexperience of worksheets so let me try again: Column A shows the catalogue number I have allocated to the recording (cd's, for example, have a label on them showing this number) When I buy a new recording, either cd or mp3 download, I allocate the next number. This was easy when the numbers were in sequential order but now, after sorting the sheet in alphabetical order of composer listed in column D, the numbers in column A are no longer in sequential order which is to be expected. This makes it difficult to find the last number used so as to enable me to allocate the next to a newly acquired recording. I would like to like to either put a formula in the next A cell which would return this number or find it in some other way. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2[/quote] Last edited by Quilp : September 13th 13 at 05:18 PM Reason: message was shown in duplicate |
#14
|
|||
|
|||
Quote:
Column A shows the catalogue number I have allocated to the recording (cd's, for example, have a label on them showing this number) When I buy a new recording, either cd or mp3 download, I allocate the next number. This was easy when the numbers were in sequential order but now, after sorting the sheet in alphabetical order of composer listed in column D, the numbers in column A are no longer in sequential order which is to be expected. This makes it difficult to find the last number used so as to enable me to allocate the next to a newly acquired recording. I would like to like to either put a formula in the next A cell which would return this number or find it in some other way. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2[/quote][/quote] |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting rows
Hi,
Am Fri, 13 Sep 2013 17:16:50 +0100 schrieb Quilp: Column A shows the catalogue number I have allocated to the recording (cd's, for example, have a label on them showing this number) try: =MAX(A:A) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#16
|
|||
|
|||
Quote:
|
#17
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Sorting rows
Hi,
Am Sat, 14 Sep 2013 08:54:32 +0100 schrieb Quilp: I have been trying this function(?) but the results are confusing. If I try it in column A it works (not that I need to use it in this column because it is in sequential order) but when I use it in column B, which contains the same numbers but out of sequence following the sorting previously discussed, it returns zero. Is it the case that you cannot use the MAX function in columns that have been sorted? Or, perhaps it should be applied before sorting to make it work. if the MAX function returns 0 your numbers are text formatted. Write a 1 in an empty cell, copy this cell, select all numbers in column B, Insert = Paste Special = Multiply Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#18
|
|||
|
|||
Quote:
I should not really be using this forum because I do not have Excel but it is so much better than the PlanMaker forums. Thanks for all your help Claus - it is much appreciated. Perhaps one day, if you have nothing better to do, you could download the free version of PlanMaker and see if you can resolve this MAX problem! Regards Quilp |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting rows out of order after all rows are numbered in sequence | Excel Programming | |||
sorting rows | Excel Discussion (Misc queries) | |||
Sorting block of rows with empty rows between it | Excel Programming | |||
Sorting rows | Excel Programming | |||
[sorting rows] | Excel Worksheet Functions |