Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
I have a worksheet similar to this:
A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
Don't merge any cells.
Select all the data, including the header row (i.e. A1:E11 in your example) and choose Data | Sort from the pull-down menus. Ensure that Header Row is ticked, and you can then choose to sort by Lname followed by Fname. All the related data will be kept on the same rows - do not use the Sort icon from the toolbar, as this doesn't always work in the way you wish it to. Hope this helps. Pete On Jun 28, 1:01 am, "Hank" wrote: I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
"...do not use the Sort icon from the toolbar, as this doesn't always work in
the way you wish it to." Classic understatement! <g Hank - not to sidetrack things: Pete_UK told you how to do it and get it done the way you want/expect. "Pete_UK" wrote: Don't merge any cells. Select all the data, including the header row (i.e. A1:E11 in your example) and choose Data | Sort from the pull-down menus. Ensure that Header Row is ticked, and you can then choose to sort by Lname followed by Fname. All the related data will be kept on the same rows - do not use the Sort icon from the toolbar, as this doesn't always work in the way you wish it to. Hope this helps. Pete On Jun 28, 1:01 am, "Hank" wrote: I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
Thanks for the help. Looks my example did not print out like I wanted.
Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. Thanks again Hank "Hank" wrote in message ... I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
OK, I begin to see what you are having a problem with... Let's use an .x. to
show where empty cells are, and what I see you having is one person's name in columns A and B with some relative in C, D and E. Then below that same person you may have other relatives in C, D and E for them, but you have not entered their names (saving typing and perhaps making seeing relationships easier), as: A B C D E 1 LName FName Rel. RFName RLName 2 Jones John Dad Bill Jones 3 .x. .x. Mom Ella Smith 4 .x. .x. Sis Joan Jones 5 Brown Mary .... .... .... and that situation does cause Excel to sort them in a rather unpleasant manner by putting all of the entries with empty cells in A and B at the bottom of the list. I've prepared some code below that should solve this problem for you. You do need to make one change to your layout. I strongly suggest (and the code expects it) that you insert a new column A and label it Sequence. In that column will be numeric entries indicating the sequence you made an entry into that row in. This will help keep groups consisting of same named individual in the order you entered them in initially (Dad, Mom, Sister) when they are sorted. It also gives you a way to go back to ground zero with the list should anything get messed up later on. That pushes LName into B, FName into C and Relationship into D. Like this: A B C D E F 1 Sequence LName FName Rel. RFName RLName 2 1 Jones John Dad Bill Jones 3 2 .x. .x. Mom Ella Smith 4 3 .x. .x. Sis Joan Jones 5 4 Brown Mary .... .... .... 6 5 .x. .x. .... .... .... with this layout, the code below will become functional. To insert the code into the workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module from its menu bar. Copy and paste all of the code below into the module and close the VB Editor. Now to fill empty cells, sort the names and then undo the 'fill' operation all you have to do is use Tools | Macro | Macros and [Run] the SortFamilyEntries macro. The code also assumes that there is always some entry in the Relationship column all the way down through all "rows of interest", i.e. those that are to be sorted. What they do: FillForSorting fills empty cells based on previous name entry (it does expect there to be a real name entry at row 2 at a minimum). It then callse SortThem to sort by: LName, FName, Sequence Then DeleteCreatedDuplicates goes through and finds the formulas that FillForSorting created and removes them, making the cells empty again! Sub SortFamilyEntries() FillForSorting SortThem DeleteCreatedDuplicates End Sub Sub FillForSorting() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'duplicate names into empty cells 'check if column B needs LName If IsEmpty(baseCell.Offset(rOffset, -2)) Then baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" End If 'check if column C needs FName If IsEmpty(baseCell.Offset(rOffset, -1)) Then baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub DeleteCreatedDuplicates() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'erase duplicated names in cells 'check if column B has formula, not a name If baseCell.Offset(rOffset, -2).HasFormula Then If baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -2) = "" End If End If 'check if column C has formula, not a name If baseCell.Offset(rOffset, -1).HasFormula Then If baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -1) = "" End If End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub SortThem() 'change any of these Const 'values as required for sheet layout 'address of first LName entry Const LastName = "B2" 'address of first FName entry Const FirstName = "C2" 'address of first Sequence # Const Sequence = "A2" 'last column with data to include in sort Const lastColToSort = "F" 'column with the Relationship entry in it Const relColumn = "D" Dim lastRelRow As Long Dim sortRange As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If Set sortRange = Range("A1:" & lastColToSort & lastRelRow) sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("B1").Select End Sub "Hank" wrote: Thanks for the help. Looks my example did not print out like I wanted. Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. Thanks again Hank "Hank" wrote in message ... I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
Thanks for your hard work.
Looks like it's almost right. The only problem I found is that it also sorts the header row. How can I tweak it to fix that? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... OK, I begin to see what you are having a problem with... Let's use an .x. to show where empty cells are, and what I see you having is one person's name in columns A and B with some relative in C, D and E. Then below that same person you may have other relatives in C, D and E for them, but you have not entered their names (saving typing and perhaps making seeing relationships easier), as: A B C D E 1 LName FName Rel. RFName RLName 2 Jones John Dad Bill Jones 3 .x. .x. Mom Ella Smith 4 .x. .x. Sis Joan Jones 5 Brown Mary .... .... .... and that situation does cause Excel to sort them in a rather unpleasant manner by putting all of the entries with empty cells in A and B at the bottom of the list. I've prepared some code below that should solve this problem for you. You do need to make one change to your layout. I strongly suggest (and the code expects it) that you insert a new column A and label it Sequence. In that column will be numeric entries indicating the sequence you made an entry into that row in. This will help keep groups consisting of same named individual in the order you entered them in initially (Dad, Mom, Sister) when they are sorted. It also gives you a way to go back to ground zero with the list should anything get messed up later on. That pushes LName into B, FName into C and Relationship into D. Like this: A B C D E F 1 Sequence LName FName Rel. RFName RLName 2 1 Jones John Dad Bill Jones 3 2 .x. .x. Mom Ella Smith 4 3 .x. .x. Sis Joan Jones 5 4 Brown Mary .... .... .... 6 5 .x. .x. .... .... .... with this layout, the code below will become functional. To insert the code into the workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module from its menu bar. Copy and paste all of the code below into the module and close the VB Editor. Now to fill empty cells, sort the names and then undo the 'fill' operation all you have to do is use Tools | Macro | Macros and [Run] the SortFamilyEntries macro. The code also assumes that there is always some entry in the Relationship column all the way down through all "rows of interest", i.e. those that are to be sorted. What they do: FillForSorting fills empty cells based on previous name entry (it does expect there to be a real name entry at row 2 at a minimum). It then callse SortThem to sort by: LName, FName, Sequence Then DeleteCreatedDuplicates goes through and finds the formulas that FillForSorting created and removes them, making the cells empty again! Sub SortFamilyEntries() FillForSorting SortThem DeleteCreatedDuplicates End Sub Sub FillForSorting() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'duplicate names into empty cells 'check if column B needs LName If IsEmpty(baseCell.Offset(rOffset, -2)) Then baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" End If 'check if column C needs FName If IsEmpty(baseCell.Offset(rOffset, -1)) Then baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub DeleteCreatedDuplicates() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'erase duplicated names in cells 'check if column B has formula, not a name If baseCell.Offset(rOffset, -2).HasFormula Then If baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -2) = "" End If End If 'check if column C has formula, not a name If baseCell.Offset(rOffset, -1).HasFormula Then If baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -1) = "" End If End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub SortThem() 'change any of these Const 'values as required for sheet layout 'address of first LName entry Const LastName = "B2" 'address of first FName entry Const FirstName = "C2" 'address of first Sequence # Const Sequence = "A2" 'last column with data to include in sort Const lastColToSort = "F" 'column with the Relationship entry in it Const relColumn = "D" Dim lastRelRow As Long Dim sortRange As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If Set sortRange = Range("A1:" & lastColToSort & lastRelRow) sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("B1").Select End Sub "Hank" wrote: Thanks for the help. Looks my example did not print out like I wanted. Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. Thanks again Hank "Hank" wrote in message ... I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
try changing the
Header:=xlYes, parameter down in the sort to Header:=xlGuess, "Hank" wrote: Thanks for your hard work. Looks like it's almost right. The only problem I found is that it also sorts the header row. How can I tweak it to fix that? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... OK, I begin to see what you are having a problem with... Let's use an .x. to show where empty cells are, and what I see you having is one person's name in columns A and B with some relative in C, D and E. Then below that same person you may have other relatives in C, D and E for them, but you have not entered their names (saving typing and perhaps making seeing relationships easier), as: A B C D E 1 LName FName Rel. RFName RLName 2 Jones John Dad Bill Jones 3 .x. .x. Mom Ella Smith 4 .x. .x. Sis Joan Jones 5 Brown Mary .... .... .... and that situation does cause Excel to sort them in a rather unpleasant manner by putting all of the entries with empty cells in A and B at the bottom of the list. I've prepared some code below that should solve this problem for you. You do need to make one change to your layout. I strongly suggest (and the code expects it) that you insert a new column A and label it Sequence. In that column will be numeric entries indicating the sequence you made an entry into that row in. This will help keep groups consisting of same named individual in the order you entered them in initially (Dad, Mom, Sister) when they are sorted. It also gives you a way to go back to ground zero with the list should anything get messed up later on. That pushes LName into B, FName into C and Relationship into D. Like this: A B C D E F 1 Sequence LName FName Rel. RFName RLName 2 1 Jones John Dad Bill Jones 3 2 .x. .x. Mom Ella Smith 4 3 .x. .x. Sis Joan Jones 5 4 Brown Mary .... .... .... 6 5 .x. .x. .... .... .... with this layout, the code below will become functional. To insert the code into the workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module from its menu bar. Copy and paste all of the code below into the module and close the VB Editor. Now to fill empty cells, sort the names and then undo the 'fill' operation all you have to do is use Tools | Macro | Macros and [Run] the SortFamilyEntries macro. The code also assumes that there is always some entry in the Relationship column all the way down through all "rows of interest", i.e. those that are to be sorted. What they do: FillForSorting fills empty cells based on previous name entry (it does expect there to be a real name entry at row 2 at a minimum). It then callse SortThem to sort by: LName, FName, Sequence Then DeleteCreatedDuplicates goes through and finds the formulas that FillForSorting created and removes them, making the cells empty again! Sub SortFamilyEntries() FillForSorting SortThem DeleteCreatedDuplicates End Sub Sub FillForSorting() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'duplicate names into empty cells 'check if column B needs LName If IsEmpty(baseCell.Offset(rOffset, -2)) Then baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" End If 'check if column C needs FName If IsEmpty(baseCell.Offset(rOffset, -1)) Then baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub DeleteCreatedDuplicates() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'erase duplicated names in cells 'check if column B has formula, not a name If baseCell.Offset(rOffset, -2).HasFormula Then If baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -2) = "" End If End If 'check if column C has formula, not a name If baseCell.Offset(rOffset, -1).HasFormula Then If baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -1) = "" End If End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub SortThem() 'change any of these Const 'values as required for sheet layout 'address of first LName entry Const LastName = "B2" 'address of first FName entry Const FirstName = "C2" 'address of first Sequence # Const Sequence = "A2" 'last column with data to include in sort Const lastColToSort = "F" 'column with the Relationship entry in it Const relColumn = "D" Dim lastRelRow As Long Dim sortRange As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If Set sortRange = Range("A1:" & lastColToSort & lastRelRow) sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("B1").Select End Sub "Hank" wrote: Thanks for the help. Looks my example did not print out like I wanted. Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. Thanks again Hank "Hank" wrote in message ... I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
Works better except I get - Run Time error'1004': "Operaton requires merged
cells to be identically sized" This show highlighted: sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom But the form is properly sorted except for the duplicate entries. If I then run the DeleteCreatedDuplicates macro - the work sheet is sorted just the way I wanted. What can I do to stop the run time error? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... try changing the Header:=xlYes, parameter down in the sort to Header:=xlGuess, "Hank" wrote: Thanks for your hard work. Looks like it's almost right. The only problem I found is that it also sorts the header row. How can I tweak it to fix that? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... OK, I begin to see what you are having a problem with... Let's use an .x. to show where empty cells are, and what I see you having is one person's name in columns A and B with some relative in C, D and E. Then below that same person you may have other relatives in C, D and E for them, but you have not entered their names (saving typing and perhaps making seeing relationships easier), as: A B C D E 1 LName FName Rel. RFName RLName 2 Jones John Dad Bill Jones 3 .x. .x. Mom Ella Smith 4 .x. .x. Sis Joan Jones 5 Brown Mary .... .... .... and that situation does cause Excel to sort them in a rather unpleasant manner by putting all of the entries with empty cells in A and B at the bottom of the list. I've prepared some code below that should solve this problem for you. You do need to make one change to your layout. I strongly suggest (and the code expects it) that you insert a new column A and label it Sequence. In that column will be numeric entries indicating the sequence you made an entry into that row in. This will help keep groups consisting of same named individual in the order you entered them in initially (Dad, Mom, Sister) when they are sorted. It also gives you a way to go back to ground zero with the list should anything get messed up later on. That pushes LName into B, FName into C and Relationship into D. Like this: A B C D E F 1 Sequence LName FName Rel. RFName RLName 2 1 Jones John Dad Bill Jones 3 2 .x. .x. Mom Ella Smith 4 3 .x. .x. Sis Joan Jones 5 4 Brown Mary .... .... .... 6 5 .x. .x. .... .... .... with this layout, the code below will become functional. To insert the code into the workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module from its menu bar. Copy and paste all of the code below into the module and close the VB Editor. Now to fill empty cells, sort the names and then undo the 'fill' operation all you have to do is use Tools | Macro | Macros and [Run] the SortFamilyEntries macro. The code also assumes that there is always some entry in the Relationship column all the way down through all "rows of interest", i.e. those that are to be sorted. What they do: FillForSorting fills empty cells based on previous name entry (it does expect there to be a real name entry at row 2 at a minimum). It then callse SortThem to sort by: LName, FName, Sequence Then DeleteCreatedDuplicates goes through and finds the formulas that FillForSorting created and removes them, making the cells empty again! Sub SortFamilyEntries() FillForSorting SortThem DeleteCreatedDuplicates End Sub Sub FillForSorting() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'duplicate names into empty cells 'check if column B needs LName If IsEmpty(baseCell.Offset(rOffset, -2)) Then baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" End If 'check if column C needs FName If IsEmpty(baseCell.Offset(rOffset, -1)) Then baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub DeleteCreatedDuplicates() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'erase duplicated names in cells 'check if column B has formula, not a name If baseCell.Offset(rOffset, -2).HasFormula Then If baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -2) = "" End If End If 'check if column C has formula, not a name If baseCell.Offset(rOffset, -1).HasFormula Then If baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -1) = "" End If End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub SortThem() 'change any of these Const 'values as required for sheet layout 'address of first LName entry Const LastName = "B2" 'address of first FName entry Const FirstName = "C2" 'address of first Sequence # Const Sequence = "A2" 'last column with data to include in sort Const lastColToSort = "F" 'column with the Relationship entry in it Const relColumn = "D" Dim lastRelRow As Long Dim sortRange As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If Set sortRange = Range("A1:" & lastColToSort & lastRelRow) sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("B1").Select End Sub "Hank" wrote: Thanks for the help. Looks my example did not print out like I wanted. Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. Thanks again Hank "Hank" wrote in message ... I have a worksheet similar to this: A B C D E 1 Fname Lname Relative RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early 12 Row 1 is the header row. I want to sort Col A and/or Col B but keep the relative infromation with the right people. I think I could do it if I had the FName and LName typed in each cell rather than leaving them blank, but it looks odd. Is there a better way? I've tried merging cells A2:A4 and B2:B4, etc but it will not sort properly. Hank |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting Blank Cells
Now you know one of the reasons we so often recommend not using merged cells.
They cause all kinds of 'hidden' problems like this one. temporarily rewrite this one routine: Sub SortFamilyEntries() FillForSorting Exit Sub ' just fill up for temporary testing SortThem DeleteCreatedDuplicates End Sub Run that and it'll just fill things up and then quit. Now record a macro while you go through the whole operation of selecting the cells to be sorted and sorting them by Last name, first name and sequence #. Modify the code recorded you record to look much like the code in the sorting area that I provided and put it in place of the .Sort code there. You'll need to change what you record from Selection.Sort to sortRange.Sort and make other changes to it dealing with the Range(...) parameters it recorded so it blends into the SortThem() routine properly. If that doesn't go well for you, copy the code that your macro recorded and post it here and we'll try to help revise it to work. Best thing would be to get rid of those darned merged cells! "Hank" wrote: Works better except I get - Run Time error'1004': "Operaton requires merged cells to be identically sized" This show highlighted: sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom But the form is properly sorted except for the duplicate entries. If I then run the DeleteCreatedDuplicates macro - the work sheet is sorted just the way I wanted. What can I do to stop the run time error? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... try changing the Header:=xlYes, parameter down in the sort to Header:=xlGuess, "Hank" wrote: Thanks for your hard work. Looks like it's almost right. The only problem I found is that it also sorts the header row. How can I tweak it to fix that? Hank "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... OK, I begin to see what you are having a problem with... Let's use an .x. to show where empty cells are, and what I see you having is one person's name in columns A and B with some relative in C, D and E. Then below that same person you may have other relatives in C, D and E for them, but you have not entered their names (saving typing and perhaps making seeing relationships easier), as: A B C D E 1 LName FName Rel. RFName RLName 2 Jones John Dad Bill Jones 3 .x. .x. Mom Ella Smith 4 .x. .x. Sis Joan Jones 5 Brown Mary .... .... .... and that situation does cause Excel to sort them in a rather unpleasant manner by putting all of the entries with empty cells in A and B at the bottom of the list. I've prepared some code below that should solve this problem for you. You do need to make one change to your layout. I strongly suggest (and the code expects it) that you insert a new column A and label it Sequence. In that column will be numeric entries indicating the sequence you made an entry into that row in. This will help keep groups consisting of same named individual in the order you entered them in initially (Dad, Mom, Sister) when they are sorted. It also gives you a way to go back to ground zero with the list should anything get messed up later on. That pushes LName into B, FName into C and Relationship into D. Like this: A B C D E F 1 Sequence LName FName Rel. RFName RLName 2 1 Jones John Dad Bill Jones 3 2 .x. .x. Mom Ella Smith 4 3 .x. .x. Sis Joan Jones 5 4 Brown Mary .... .... .... 6 5 .x. .x. .... .... .... with this layout, the code below will become functional. To insert the code into the workbook, press [Alt]+[F11] to enter the VB Editor. Choose Insert | Module from its menu bar. Copy and paste all of the code below into the module and close the VB Editor. Now to fill empty cells, sort the names and then undo the 'fill' operation all you have to do is use Tools | Macro | Macros and [Run] the SortFamilyEntries macro. The code also assumes that there is always some entry in the Relationship column all the way down through all "rows of interest", i.e. those that are to be sorted. What they do: FillForSorting fills empty cells based on previous name entry (it does expect there to be a real name entry at row 2 at a minimum). It then callse SortThem to sort by: LName, FName, Sequence Then DeleteCreatedDuplicates goes through and finds the formulas that FillForSorting created and removes them, making the cells empty again! Sub SortFamilyEntries() FillForSorting SortThem DeleteCreatedDuplicates End Sub Sub FillForSorting() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'duplicate names into empty cells 'check if column B needs LName If IsEmpty(baseCell.Offset(rOffset, -2)) Then baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" End If 'check if column C needs FName If IsEmpty(baseCell.Offset(rOffset, -1)) Then baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub DeleteCreatedDuplicates() 'change to column with relationship in it Const relColumn = "D" Dim lastRelRow As Long Dim rOffset As Long Dim baseCell As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If 'expects there will always be a name in row 2 Set baseCell = Range(relColumn & 2) ' D2 rOffset = 1 ' initialize Application.ScreenUpdating = False Do While baseCell.Row + rOffset <= lastRelRow 'erase duplicated names in cells 'check if column B has formula, not a name If baseCell.Offset(rOffset, -2).HasFormula Then If baseCell.Offset(rOffset, -2).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -2) = "" End If End If 'check if column C has formula, not a name If baseCell.Offset(rOffset, -1).HasFormula Then If baseCell.Offset(rOffset, -1).FormulaR1C1 = _ "=R[-1]C" Then baseCell.Offset(rOffset, -1) = "" End If End If rOffset = rOffset + 1 Loop Application.ScreenUpdating = True End Sub Sub SortThem() 'change any of these Const 'values as required for sheet layout 'address of first LName entry Const LastName = "B2" 'address of first FName entry Const FirstName = "C2" 'address of first Sequence # Const Sequence = "A2" 'last column with data to include in sort Const lastColToSort = "F" 'column with the Relationship entry in it Const relColumn = "D" Dim lastRelRow As Long Dim sortRange As Range If Val(Left(Application.Version, 2)) < 12 Then 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.Count).End(xlUp).Row Else 'in pre-2007 Excel lastRelRow = _ Range(relColumn & Rows.CountLarge).End(xlUp).Row End If If lastRelRow < 3 Then 'nothing to do, really Exit Sub End If Set sortRange = Range("A1:" & lastColToSort & lastRelRow) sortRange.Sort Key1:=Range(LastName), Order1:=xlAscending, _ Key2:=Range(FirstName), Order2:=xlAscending, _ Key3:=Range(Sequence), Order3:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("B1").Select End Sub "Hank" wrote: Thanks for the help. Looks my example did not print out like I wanted. Hopefully the following will show up better. A B C D E 1 FName LName Relationship RelFName RelLName 2 Jim Jones Dad Robert Jones 3 Mom Nancy Jones 4 Sister Haley Jones 5 Liz White Mom Beth White 6 Dad Bill White 7 Uncle Joe Jackson 8 Grandfather Earl Wilson 9 Frank Anderson Dad Jim Anderson 10 Mom Betty Anderson 11 Grandfather Harry Early I want to sort only col A and B. The Col C - E must remain with the correct names shown in Col A and B. I have not been able to do this by selecting the sort field and using the "sort" drop down menu even when I check "Header Row". If I put "Jim" in A3, and A4, "Jones" in B3 and B4 and duplicate the other names in the blank cells it will work OK - just does not look as good. I have type the names in the blank cells and used a white font color for the duplicates - works OK, but is there another way. Hope my example shows up better this time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Sorting with blank cells | Excel Discussion (Misc queries) | |||
Sorting and Blank Cells | Excel Worksheet Functions | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions |