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

[Guest]

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.

 

[Anne Troy]

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

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

 

[Gord Dibben]

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

 

[Guest]

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

 

[Anne Troy]

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

 

[Debra Dalgleish]

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/xlCondFormat03.html#Duplicate