+ Reply to Thread
Results 1 to 9 of 9

Sort a list with a formula

  1. #1
    Steve-in-austin
    Guest

    Sort a list with a formula

    Is there a way to sort a column with a series of formulas? ie select the
    range and return the lowest value in row 1, next value in row 2 etc. The
    range may contain blanks, which should sort to the bottom.
    Thanks in advance!

  2. #2
    Don Guillett
    Guest

    Re: Sort a list with a formula

    you could use LARGE or SMALL but why?

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    > Is there a way to sort a column with a series of formulas? ie select the
    > range and return the lowest value in row 1, next value in row 2 etc. The
    > range may contain blanks, which should sort to the bottom.
    > Thanks in advance!




  3. #3
    Steve-in-austin
    Guest

    Re: Sort a list with a formula

    I am attempting to automate a forecasting model as much as possible without
    the need for users to presort their phase numbering There may be multiple
    occurances of the same sort character(s) so SMALL will not work without some
    additional logic. For example, the list:
    01
    02
    01
    03
    should sort to
    01
    01
    02
    03

    "Don Guillett" wrote:

    > you could use LARGE or SMALL but why?
    >
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    > news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    > > Is there a way to sort a column with a series of formulas? ie select the
    > > range and return the lowest value in row 1, next value in row 2 etc. The
    > > range may contain blanks, which should sort to the bottom.
    > > Thanks in advance!

    >
    >
    >


  4. #4
    Don Guillett
    Guest

    Re: Sort a list with a formula

    try
    =SMALL($G$1:$G$4,ROW())

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    >I am attempting to automate a forecasting model as much as possible without
    > the need for users to presort their phase numbering There may be multiple
    > occurances of the same sort character(s) so SMALL will not work without
    > some
    > additional logic. For example, the list:
    > 01
    > 02
    > 01
    > 03
    > should sort to
    > 01
    > 01
    > 02
    > 03
    >
    > "Don Guillett" wrote:
    >
    >> you could use LARGE or SMALL but why?
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> dguillett1@austin.rr.com
    >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >> message
    >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    >> > Is there a way to sort a column with a series of formulas? ie select
    >> > the
    >> > range and return the lowest value in row 1, next value in row 2 etc.
    >> > The
    >> > range may contain blanks, which should sort to the bottom.
    >> > Thanks in advance!

    >>
    >>
    >>




  5. #5
    Steve-in-austin
    Guest

    Re: Sort a list with a formula

    This works well if the data begins in row 1. Is there a way to compensate for
    data that does not start in row 1?

    "Don Guillett" wrote:

    > try
    > =SMALL($G$1:$G$4,ROW())
    >
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    > news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    > >I am attempting to automate a forecasting model as much as possible without
    > > the need for users to presort their phase numbering There may be multiple
    > > occurances of the same sort character(s) so SMALL will not work without
    > > some
    > > additional logic. For example, the list:
    > > 01
    > > 02
    > > 01
    > > 03
    > > should sort to
    > > 01
    > > 01
    > > 02
    > > 03
    > >
    > > "Don Guillett" wrote:
    > >
    > >> you could use LARGE or SMALL but why?
    > >>
    > >> --
    > >> Don Guillett
    > >> SalesAid Software
    > >> dguillett1@austin.rr.com
    > >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    > >> message
    > >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    > >> > Is there a way to sort a column with a series of formulas? ie select
    > >> > the
    > >> > range and return the lowest value in row 1, next value in row 2 etc.
    > >> > The
    > >> > range may contain blanks, which should sort to the bottom.
    > >> > Thanks in advance!
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Don Guillett
    Guest

    Re: Sort a list with a formula

    try
    =SMALL($G$2:$G$5,ROW(A1))
    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    news:8716271A-58B5-4A86-B148-7429E461CC3C@microsoft.com...
    > This works well if the data begins in row 1. Is there a way to compensate
    > for
    > data that does not start in row 1?
    >
    > "Don Guillett" wrote:
    >
    >> try
    >> =SMALL($G$1:$G$4,ROW())
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> dguillett1@austin.rr.com
    >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >> message
    >> news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    >> >I am attempting to automate a forecasting model as much as possible
    >> >without
    >> > the need for users to presort their phase numbering There may be
    >> > multiple
    >> > occurances of the same sort character(s) so SMALL will not work without
    >> > some
    >> > additional logic. For example, the list:
    >> > 01
    >> > 02
    >> > 01
    >> > 03
    >> > should sort to
    >> > 01
    >> > 01
    >> > 02
    >> > 03
    >> >
    >> > "Don Guillett" wrote:
    >> >
    >> >> you could use LARGE or SMALL but why?
    >> >>
    >> >> --
    >> >> Don Guillett
    >> >> SalesAid Software
    >> >> dguillett1@austin.rr.com
    >> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    >> >> > Is there a way to sort a column with a series of formulas? ie select
    >> >> > the
    >> >> > range and return the lowest value in row 1, next value in row 2 etc.
    >> >> > The
    >> >> > range may contain blanks, which should sort to the bottom.
    >> >> > Thanks in advance!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  7. #7
    Steve-in-austin
    Guest

    Re: Sort a list with a formula

    Thanks Don, that was too easy!

    "Don Guillett" wrote:

    > try
    > =SMALL($G$2:$G$5,ROW(A1))
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    > news:8716271A-58B5-4A86-B148-7429E461CC3C@microsoft.com...
    > > This works well if the data begins in row 1. Is there a way to compensate
    > > for
    > > data that does not start in row 1?
    > >
    > > "Don Guillett" wrote:
    > >
    > >> try
    > >> =SMALL($G$1:$G$4,ROW())
    > >>
    > >> --
    > >> Don Guillett
    > >> SalesAid Software
    > >> dguillett1@austin.rr.com
    > >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    > >> message
    > >> news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    > >> >I am attempting to automate a forecasting model as much as possible
    > >> >without
    > >> > the need for users to presort their phase numbering There may be
    > >> > multiple
    > >> > occurances of the same sort character(s) so SMALL will not work without
    > >> > some
    > >> > additional logic. For example, the list:
    > >> > 01
    > >> > 02
    > >> > 01
    > >> > 03
    > >> > should sort to
    > >> > 01
    > >> > 01
    > >> > 02
    > >> > 03
    > >> >
    > >> > "Don Guillett" wrote:
    > >> >
    > >> >> you could use LARGE or SMALL but why?
    > >> >>
    > >> >> --
    > >> >> Don Guillett
    > >> >> SalesAid Software
    > >> >> dguillett1@austin.rr.com
    > >> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    > >> >> message
    > >> >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    > >> >> > Is there a way to sort a column with a series of formulas? ie select
    > >> >> > the
    > >> >> > range and return the lowest value in row 1, next value in row 2 etc.
    > >> >> > The
    > >> >> > range may contain blanks, which should sort to the bottom.
    > >> >> > Thanks in advance!
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >


  8. #8
    Don Guillett
    Guest

    Re: Sort a list with a formula

    Glad to help

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in message
    news:10A5D36F-1F48-48EF-9EB2-BD1F9A19CDEE@microsoft.com...
    > Thanks Don, that was too easy!
    >
    > "Don Guillett" wrote:
    >
    >> try
    >> =SMALL($G$2:$G$5,ROW(A1))
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> dguillett1@austin.rr.com
    >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >> message
    >> news:8716271A-58B5-4A86-B148-7429E461CC3C@microsoft.com...
    >> > This works well if the data begins in row 1. Is there a way to
    >> > compensate
    >> > for
    >> > data that does not start in row 1?
    >> >
    >> > "Don Guillett" wrote:
    >> >
    >> >> try
    >> >> =SMALL($G$1:$G$4,ROW())
    >> >>
    >> >> --
    >> >> Don Guillett
    >> >> SalesAid Software
    >> >> dguillett1@austin.rr.com
    >> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    >> >> >I am attempting to automate a forecasting model as much as possible
    >> >> >without
    >> >> > the need for users to presort their phase numbering There may be
    >> >> > multiple
    >> >> > occurances of the same sort character(s) so SMALL will not work
    >> >> > without
    >> >> > some
    >> >> > additional logic. For example, the list:
    >> >> > 01
    >> >> > 02
    >> >> > 01
    >> >> > 03
    >> >> > should sort to
    >> >> > 01
    >> >> > 01
    >> >> > 02
    >> >> > 03
    >> >> >
    >> >> > "Don Guillett" wrote:
    >> >> >
    >> >> >> you could use LARGE or SMALL but why?
    >> >> >>
    >> >> >> --
    >> >> >> Don Guillett
    >> >> >> SalesAid Software
    >> >> >> dguillett1@austin.rr.com
    >> >> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote
    >> >> >> in
    >> >> >> message
    >> >> >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    >> >> >> > Is there a way to sort a column with a series of formulas? ie
    >> >> >> > select
    >> >> >> > the
    >> >> >> > range and return the lowest value in row 1, next value in row 2
    >> >> >> > etc.
    >> >> >> > The
    >> >> >> > range may contain blanks, which should sort to the bottom.
    >> >> >> > Thanks in advance!
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  9. #9
    Don Guillett
    Guest

    Re: Sort a list with a formula

    I meant to say, glad to help a fellow Austinite.

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "Don Guillett" <dguillett1@austin.rr.com> wrote in message
    news:%23dK4OFefGHA.4276@TK2MSFTNGP03.phx.gbl...
    > Glad to help
    >
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    > message news:10A5D36F-1F48-48EF-9EB2-BD1F9A19CDEE@microsoft.com...
    >> Thanks Don, that was too easy!
    >>
    >> "Don Guillett" wrote:
    >>
    >>> try
    >>> =SMALL($G$2:$G$5,ROW(A1))
    >>> --
    >>> Don Guillett
    >>> SalesAid Software
    >>> dguillett1@austin.rr.com
    >>> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >>> message
    >>> news:8716271A-58B5-4A86-B148-7429E461CC3C@microsoft.com...
    >>> > This works well if the data begins in row 1. Is there a way to
    >>> > compensate
    >>> > for
    >>> > data that does not start in row 1?
    >>> >
    >>> > "Don Guillett" wrote:
    >>> >
    >>> >> try
    >>> >> =SMALL($G$1:$G$4,ROW())
    >>> >>
    >>> >> --
    >>> >> Don Guillett
    >>> >> SalesAid Software
    >>> >> dguillett1@austin.rr.com
    >>> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote in
    >>> >> message
    >>> >> news:73DF1143-4FDE-4581-8896-2E626F909D74@microsoft.com...
    >>> >> >I am attempting to automate a forecasting model as much as possible
    >>> >> >without
    >>> >> > the need for users to presort their phase numbering There may be
    >>> >> > multiple
    >>> >> > occurances of the same sort character(s) so SMALL will not work
    >>> >> > without
    >>> >> > some
    >>> >> > additional logic. For example, the list:
    >>> >> > 01
    >>> >> > 02
    >>> >> > 01
    >>> >> > 03
    >>> >> > should sort to
    >>> >> > 01
    >>> >> > 01
    >>> >> > 02
    >>> >> > 03
    >>> >> >
    >>> >> > "Don Guillett" wrote:
    >>> >> >
    >>> >> >> you could use LARGE or SMALL but why?
    >>> >> >>
    >>> >> >> --
    >>> >> >> Don Guillett
    >>> >> >> SalesAid Software
    >>> >> >> dguillett1@austin.rr.com
    >>> >> >> "Steve-in-austin" <Steveinaustin@discussions.microsoft.com> wrote
    >>> >> >> in
    >>> >> >> message
    >>> >> >> news:EB940315-4425-43A8-87BD-E3D492EA2E83@microsoft.com...
    >>> >> >> > Is there a way to sort a column with a series of formulas? ie
    >>> >> >> > select
    >>> >> >> > the
    >>> >> >> > range and return the lowest value in row 1, next value in row 2
    >>> >> >> > etc.
    >>> >> >> > The
    >>> >> >> > range may contain blanks, which should sort to the bottom.
    >>> >> >> > Thanks in advance!
    >>> >> >>
    >>> >> >>
    >>> >> >>
    >>> >>
    >>> >>
    >>> >>
    >>>
    >>>
    >>>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1