Sorting rows

Discussion in 'Excel Beginners' started by Quilp, Sep 8, 2013.

  1. Quilp

    Quilp Guest

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

    Can anyone tell me if and how this can be achieved


    --
    Quilp
     
    Quilp, Sep 8, 2013
    #1
    1. Advertisements

  2. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 8, 2013
    #2
    1. Advertisements

  3. Quilp

    Quilp Guest

    Claus Busch;1613775 Wrote:
    > 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!




    --
    Quilp
     
    Quilp, Sep 9, 2013
    #3
  4. Quilp

    Quilp Guest

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

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


    --
    Quilp
     
    Quilp, Sep 11, 2013
    #4
  5. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 11, 2013
    #5
  6. Quilp

    Quilp Guest

    Claus Busch;1613822 Wrote:
    > 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 the

    > 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


    --
    Quilp
     
    Quilp, Sep 12, 2013
    #6
  7. Quilp

    Quilp Guest

    Quilp;1613827 Wrote:
    > Thank you Claus - your help to this old newbie is much appreciated.


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


    --
    Quilp
     
    Quilp, Sep 12, 2013
    #7
  8. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 12, 2013
    #8
  9. Quilp

    Quilp Guest

    Claus Busch;1613831 Wrote:
    > 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 t

    > 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 t
    > find the highest number so that I can allocate the next one to a newl
    > 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 m
    > cataloguing sequence by inserting an extra column, now column A, an
    > entering sequential numbers into it. However, this seems a clumsy wa
    > to do it



    --
    Quilp
     
    Quilp, Sep 13, 2013
    #9
  10. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 13, 2013
    #10
  11. Quilp

    Quilp Guest

    Claus Busch;1613842 Wrote:
    > Hi,
    >
    >
    >
    > 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 clums

    > 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


    The numbers, now out of sequence, are in column


    --
    Quilp
     
    Quilp, Sep 13, 2013
    #11
  12. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 13, 2013
    #12
  13. Quilp

    Quilp Guest

    Regard
    Claus B
    --
    Win XP PRof SP2 / Vista Ultimate SP
    Office 2003 SP2 /2007 Ultimate SP

    I can't see a file format listed in the "Attach Files" which allows me t
    attach my worksheet. I apologise if my posts are confusing as a result o
    inexperience of worksheets so let me try again
    Column A shows the catalogue number I have allocated to the recordin
    (cd's, for example, have a label on them showing this number) When I buy
    new recording, either cd or mp3 download, I allocate the next number.
    This was easy when the numbers were in sequential order but now, afte
    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 b
    expected. This makes it difficult to find the last number used so as t
    enable me to allocate the next to a newly acquired recording. I would lik
    to like to either put a formula in the next A cell which would return thi
    number or find it in some other way
    --
    Win XP PRof SP2 / Vista Ultimate SP
    Office 2003 SP2 /2007 Ultimate SP


    --
    Quilp
     
    Quilp, Sep 13, 2013
    #13
  14. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 13, 2013
    #14
  15. Quilp

    Quilp Guest

    Claus Busch;1613852 Wrote:
    > 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


    I have been trying this function(?) but the results are confusing. If
    try it in column A it works (not that I need to use it in this colum
    because it is in sequential order) but when I use it in column B, whic
    contains the same numbers but out of sequence following the sortin
    previously discussed, it returns zero. Is it the case that you canno
    use the MAX function in columns that have been sorted? Or, perhaps i
    should be applied before sorting to make it work


    --
    Quilp
     
    Quilp, Sep 14, 2013
    #15
  16. Quilp

    Claus Busch Guest

    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
     
    Claus Busch, Sep 14, 2013
    #16
  17. Quilp

    Quilp Guest

    Claus Busch;1613866 Wrote:
    > Hi,
    >
    > Am Sat, 14 Sep 2013 08:54:32 +0100 schrieb Quilp:
    > -
    > > I have been trying this function(?) but the results are confusing. I

    > 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 yo

    > cannot
    > > use the MAX function in columns that have been sorted? Or, perhap

    > 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 i
    > column
    > B, Insert => Paste Special => Multiply
    >
    >
    > Regards
    > Claus B.
    > --
    > Win XP PRof SP2 / Vista Ultimate SP2
    > Office 2003 SP2 /2007 Ultimate SP2


    The PlanMaker Paste Special is, I suspect, different to Excel and I a
    unable to follow your instruction. It seems that I will have to use th
    extra column A, containing the sequential numbers, in order to locat
    the last.
    I should not really be using this forum because I do not have Excel bu
    it is so much better than the PlanMaker forums. Thanks for all your hel
    Claus - it is much appreciated. Perhaps one day, if you have nothin
    better to do, you could download the free version of PlanMaker and se
    if you can resolve this MAX problem!
    Regards
    Quil


    --
    Quilp
     
    Quilp, Sep 15, 2013
    #17
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. aukselp

    Sorting numbers acts like text sort

    aukselp, Jan 19, 2004, in forum: Excel Beginners
    Replies:
    1
    Views:
    107
    aukselp
    Jan 19, 2004
  2. DaffyD®

    Multiple Column Sorting

    DaffyD®, Jan 31, 2004, in forum: Excel Beginners
    Replies:
    16
    Views:
    122
    Gord Dibben
    Feb 2, 2004
  3. adaire
    Replies:
    2
    Views:
    59
    SteveW
    Jul 3, 2006
  4. Bob  Salzer
    Replies:
    2
    Views:
    114
    Gord Dibben
    Jul 21, 2006
  5. scotty
    Replies:
    6
    Views:
    213
    Leo Heuser
    Feb 12, 2007
  6. S R Burns

    Sorting rows of repetitive data

    S R Burns, Apr 30, 2007, in forum: Excel Beginners
    Replies:
    1
    Views:
    80
    Bob Flanagan
    Apr 30, 2007
  7. justusmomm
    Replies:
    3
    Views:
    123
    Ragdyer
    Aug 26, 2007
  8. Sort rows and move borders with rows

    , Jun 3, 2009, in forum: Excel Beginners
    Replies:
    1
    Views:
    117
    Shane Devenshire
    Jun 4, 2009
Loading...