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