+ Reply to Thread
Results 1 to 6 of 6

How do I sort a list that contains blanks that I want to keep?

  1. #1
    SHexceluser
    Guest

    How do I sort a list that contains blanks that I want to keep?

    I have copied a list from a pivot table, so there are blanks. Is there a way
    for me to sort, and maintain those blanks. For example I have a list that
    looks kind of like this:

    John Smith Red
    Orange
    Yellow
    John Doe Green
    Mary Peters Red
    Purple

    It is important to know that John Smith goes along with Red, Orange and
    Yellow. But I want to be able to sort the page so all of the people that
    only like one color are grouped together and all of the people with multiple
    colors are grouped together.


  2. #2
    Anne Troy
    Guest

    Re: How do I sort a list that contains blanks that I want to keep?

    You'll have to fill in... it's not very hard to do.
    http://www.officearticles.com/excel/...soft_excel.htm

    ************
    Anne Troy
    www.OfficeArticles.com

    "SHexceluser" <SHexceluser@discussions.microsoft.com> wrote in message
    news:F04B8E28-70ED-4D1B-B888-1AFF5A7254DF@microsoft.com...
    >I have copied a list from a pivot table, so there are blanks. Is there a
    >way
    > for me to sort, and maintain those blanks. For example I have a list that
    > looks kind of like this:
    >
    > John Smith Red
    > Orange
    > Yellow
    > John Doe Green
    > Mary Peters Red
    > Purple
    >
    > It is important to know that John Smith goes along with Red, Orange and
    > Yellow. But I want to be able to sort the page so all of the people that
    > only like one color are grouped together and all of the people with
    > multiple
    > colors are grouped together.
    >




  3. #3
    Gord Dibben
    Guest

    Re: How do I sort a list that contains blanks that I want to keep?

    Get rid of the blanks.

    Select column A then F5>Special>Blanks>OK

    With blanks selected, type an = sign in the active cell.

    Point with mouse to cell above then hit CTRL + ENTER to replicate formula
    throughout all blanks.

    You date will now look like...

    John Smith Red
    John Smith Orange
    John Smith Yellow

    etc.

    You can copy column A and paste special>values to get rid of the formulas.


    Gord Dibben Excel MVP

    On Fri, 14 Oct 2005 09:52:30 -0700, "SHexceluser"
    <SHexceluser@discussions.microsoft.com> wrote:

    >I have copied a list from a pivot table, so there are blanks. Is there a way
    >for me to sort, and maintain those blanks. For example I have a list that
    >looks kind of like this:
    >
    >John Smith Red
    > Orange
    > Yellow
    >John Doe Green
    >Mary Peters Red
    > Purple
    >
    >It is important to know that John Smith goes along with Red, Orange and
    >Yellow. But I want to be able to sort the page so all of the people that
    >only like one color are grouped together and all of the people with multiple
    >colors are grouped together.



  4. #4
    SHexceluser
    Guest

    Re: How do I sort a list that contains blanks that I want to keep?

    The problem is, I want to be able to group all of the people together that
    like multiple colors. Is there a way to sort so my list can read
    John Smith Red
    John Smith Orange
    John Smith Yellow
    Mary Peters Red
    Mary Peters Purple
    John Doe Green


    "Gord Dibben" wrote:

    > Get rid of the blanks.
    >
    > Select column A then F5>Special>Blanks>OK
    >
    > With blanks selected, type an = sign in the active cell.
    >
    > Point with mouse to cell above then hit CTRL + ENTER to replicate formula
    > throughout all blanks.
    >
    > You date will now look like...
    >
    > John Smith Red
    > John Smith Orange
    > John Smith Yellow
    >
    > etc.
    >
    > You can copy column A and paste special>values to get rid of the formulas.
    >
    >
    > Gord Dibben Excel MVP
    >
    > On Fri, 14 Oct 2005 09:52:30 -0700, "SHexceluser"
    > <SHexceluser@discussions.microsoft.com> wrote:
    >
    > >I have copied a list from a pivot table, so there are blanks. Is there a way
    > >for me to sort, and maintain those blanks. For example I have a list that
    > >looks kind of like this:
    > >
    > >John Smith Red
    > > Orange
    > > Yellow
    > >John Doe Green
    > >Mary Peters Red
    > > Purple
    > >
    > >It is important to know that John Smith goes along with Red, Orange and
    > >Yellow. But I want to be able to sort the page so all of the people that
    > >only like one color are grouped together and all of the people with multiple
    > >colors are grouped together.

    >
    >


  5. #5
    Anne Troy
    Guest

    Re: How do I sort a list that contains blanks that I want to keep?

    I would say, then, that you need to add another column to do a COUNTIF the
    cells is equal to the name. (for instance, the name is in Column A, you
    would put =countif(A:A,A1) to count how many times John Smith appears. Copy
    it down. So, John's count will appear multiple times, but should still sort
    properly by count.
    ************
    Anne Troy
    www.OfficeArticles.com

    "SHexceluser" <SHexceluser@discussions.microsoft.com> wrote in message
    news:C3B946D4-3ECC-4E1B-B5D8-836ECF9E9EDF@microsoft.com...
    > The problem is, I want to be able to group all of the people together that
    > like multiple colors. Is there a way to sort so my list can read
    > John Smith Red
    > John Smith Orange
    > John Smith Yellow
    > Mary Peters Red
    > Mary Peters Purple
    > John Doe Green
    >
    >
    > "Gord Dibben" wrote:
    >
    >> Get rid of the blanks.
    >>
    >> Select column A then F5>Special>Blanks>OK
    >>
    >> With blanks selected, type an = sign in the active cell.
    >>
    >> Point with mouse to cell above then hit CTRL + ENTER to replicate formula
    >> throughout all blanks.
    >>
    >> You date will now look like...
    >>
    >> John Smith Red
    >> John Smith Orange
    >> John Smith Yellow
    >>
    >> etc.
    >>
    >> You can copy column A and paste special>values to get rid of the
    >> formulas.
    >>
    >>
    >> Gord Dibben Excel MVP
    >>
    >> On Fri, 14 Oct 2005 09:52:30 -0700, "SHexceluser"
    >> <SHexceluser@discussions.microsoft.com> wrote:
    >>
    >> >I have copied a list from a pivot table, so there are blanks. Is there
    >> >a way
    >> >for me to sort, and maintain those blanks. For example I have a list
    >> >that
    >> >looks kind of like this:
    >> >
    >> >John Smith Red
    >> > Orange
    >> > Yellow
    >> >John Doe Green
    >> >Mary Peters Red
    >> > Purple
    >> >
    >> >It is important to know that John Smith goes along with Red, Orange and
    >> >Yellow. But I want to be able to sort the page so all of the people
    >> >that
    >> >only like one color are grouped together and all of the people with
    >> >multiple
    >> >colors are grouped together.

    >>
    >>




  6. #6
    Debra Dalgleish
    Guest

    Re: How do I sort a list that contains blanks that I want to keep?

    You could fill in the blanks, as described here:

    http://www.contextures.com/xlDataEntry02.html

    Then hide the duplicate entries with conditional formatting, as
    described here:

    http://www.contextures.com/xlCondFor...html#Duplicate

    SHexceluser wrote:
    > I have copied a list from a pivot table, so there are blanks. Is there a way
    > for me to sort, and maintain those blanks. For example I have a list that
    > looks kind of like this:
    >
    > John Smith Red
    > Orange
    > Yellow
    > John Doe Green
    > Mary Peters Red
    > Purple
    >
    > It is important to know that John Smith goes along with Red, Orange and
    > Yellow. But I want to be able to sort the page so all of the people that
    > only like one color are grouped together and all of the people with multiple
    > colors are grouped together.
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ 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