|
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. |
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" wrote in message ... 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. |
Get rid of the blanks.
Select column A then F5SpecialBlanksOK 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 specialvalues to get rid of the formulas. Gord Dibben Excel MVP On Fri, 14 Oct 2005 09:52:30 -0700, "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. |
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 F5SpecialBlanksOK 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 specialvalues to get rid of the formulas. Gord Dibben Excel MVP On Fri, 14 Oct 2005 09:52:30 -0700, "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. |
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" wrote in message ... 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 F5SpecialBlanksOK 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 specialvalues to get rid of the formulas. Gord Dibben Excel MVP On Fri, 14 Oct 2005 09:52:30 -0700, "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. |
You could fill in the blanks, as described he
http://www.contextures.com/xlDataEntry02.html Then hide the duplicate entries with conditional formatting, as described he 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 |
| All times are GMT +1. The time now is 11:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com