Solved Sort columns and keep rows together

May 30, 2012 at 10:32:18
Specs: Windows XP
I want to sort a table alphabetically by column 1 but keep the rows together when it sorts.
for example:
Smith, Joe is column 1 and a list of his family is column 2. If I add Martinez, Jesus to one and his family members to column 2 and sort how can I keep family members with Family head?

See More: Sort columns and keep rows together

Report •


#1
May 30, 2012 at 13:39:23
Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

Then please post an example of your data, both before the sort and how you want it to look after the sort.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 30, 2012 at 14:09:40
 Family	        Members	  Signature
       Smith, Joan		
	                Lizzy
       Jones, Tom		
	                Elizabeth	
	                Tom Jr.	
       Rios, Jesus		
	                Martha	
	                Johnny	
	                Sonja	
        	

After:  
           Family	Members	    Signature
       Jones, Tom		
	                Elizabeth	
	                Tom Jr.	
       Rios, Jesus		
	                 Martha	
	                 Johnny	
	                 Sonja
       Smith, Joan		
	                Lizzy


Report •

#3
May 30, 2012 at 18:50:19
✔ Best Answer
You didn't include Column letters or Row numbers so my answer is based on the following assumption. Adjust as required.


      A              B          C        D
1  Family         Members    Signature
2  Smith, Joan		
3	          Lizzy


The Excel sort feature will not allow you retain the associated data in Column B when you sort on Column A because it has no idea that the data is associated. Therefore, you are going to need a "helper column".

Enter this formula in D2 and drag it down to the bottom of the data in Column B:

=IF(A2<>"",A2,OFFSET(D2,-1,0))

This should give you the list of the names from Column A with no blanks in between the names.

      
              D
1       Smith, Joan              
2       Smith, Joan              
3       Jones, Tom               
4       Jones, Tom               
5       Jones, Tom               
6       Rios, Jesus              
7       Rios, Jesus              
8       Rios, Jesus              
9       Rios, Jesus              

Now sort Columns A:D based on Column D, then delete the list in Column D.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 31, 2012 at 06:22:16
Perfect! Thank you so much. I love excel!!!

Report •

Ask Question