Assign Values Based on First Record in Group

[Takeadoe]

All -

I've got a simple spreadsheet that looks like this:

Last Name First Name DOB Start Date End Date

Problem is this. In the first row, of each "group" there is a "group
desigination" in the "Last Name" field that I would like to assign to
all records in that group. All other values for that record are
missing. Consider the following:

Plumbrook
Smith Bob 1/30/2000 1/10/2012 1/21/2012
Jones Bill 1/4/2001 1/7/2012 1/9/2012
Ravenna
Smith Jen 1/30/2000 1/10/2012 1/21/2012
Jones Barb 1/4/2001 1/7/2012 1/9/2012


My Goal is the following:
Group Last First DOB Start End
Plumbrook Smith Bob 1/30/2000 1/10/2012 1/21/2012
Plumbrook Jones Bill 1/4/2001 1/7/2012 1/9/2012
Ravenna Smith Jen 1/30/2000 1/10/2012 1/21/2012
Ravenna Jones Barb 1/4/2001 1/7/2012 1/9/2012


Your help on this would really be appreciated. There are several
thousand records and I would rather not do this manually if there is a
way around it.


Mike

 

[Jim Cone]

Make a copy of your data.
'---
Assuming data starts in C2 with column labels in row 1 and that data extends over to Column G...
Enter this formula in B2... =IF(AND(LEN(C2),LEN(D2)=0),C2,B1) and fill down.
Copy column B and replace with values.
Consecutively number Column H cells down to bottom of data.
Sort Columns B thru H, using Column D as the sort key.
Delete the rows at the bottom.
Sort again using Column H as the sort key.
'-- -
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
Special Sort Excel add-in (30 ways to sort)





"Takeadoe" <[email protected]>
wrote in message
news:27859513.76.1331817794269.JavaMail.geo-discussion-forums@ynla18...

 

[joeu2004]

Consider the following:
Plumbrook
Smith Bob 1/30/2000 1/10/2012 1/21/2012
Jones Bill 1/4/2001 1/7/2012 1/9/2012
Ravenna
Smith Jen 1/30/2000 1/10/2012 1/21/2012
Jones Barb 1/4/2001 1/7/2012 1/9/2012

My Goal is the following:
Group Last First DOB Start End
Plumbrook Smith Bob 1/30/2000 1/10/2012 1/21/2012
Plumbrook Jones Bill 1/4/2001 1/7/2012 1/9/2012
Ravenna Smith Jen 1/30/2000 1/10/2012 1/21/2012
Ravenna Jones Barb 1/4/2001 1/7/2012 1/9/2012

You might use the macro below. Some notes:

1. It assumes that data starts in A1. If not, Change "A1" accordingly.
Alternatively, select the upper-left cell of the data, and change the Set
origDataRng statement as follows:
Set origDataRng = Range(Selection, Selection.End(xlDown)).Resize(, nCol)

2. Change Const nCol if the number of data columns is not 5.

3. To enter the macro:
a. In Excel, press alt+F11 to open the VBA window.
b. In VBA, click on Insert, then Module to open the VBA editor.
c. Copy the macro text below and paste it into the VBA editor.
d. You can now close the VBA window.

4. It would be prudent to copy the worksheet before executing the macro.

5. To execute the macro, click alt+F8 in Excel, select the macro, and press
Run.

6. To save the Excel file using XL2007 and later, be sure the file name
extension is ".xlsm" (macro enabled).

-----

Option Explicit

Sub doit()
Const nCol As Long = 5
Dim n As Long, nMax As Long, i As Long, j As Long
Dim grp As String
Dim origData As Variant, origDataRng As Range
Dim newDataRng As Range

' assumes at least 2 rows of data
Set origDataRng = Range("a1", Range("a1").End(xlDown)). _
Resize(, nCol)
origData = origDataRng
nMax = UBound(origData, 1)
ReDim newData(1 To nMax, 1 To 1 + nCol) As Variant
For i = 1 To nMax
If IsEmpty(origData(i, 2)) Then
grp = origData(i, 1)
Else
n = n + 1
newData(n, 1) = grp
For j = 1 To nCol
newData(n, j + 1) = origData(i, j)
Next
End If
Next

Application.ScreenUpdating = False
origDataRng.Resize(, 1 + nCol).Clear
Set newDataRng = origDataRng.Resize(n, 1 + nCol)
With newDataRng.Resize(1)
.Value = _
Array("Group","Last Name","First Name","DOB", _
"Start Date","End Date")
.Offset(0, 3).Resize(3).HorizontalAlignment = xlRight
End With
newDataRng.Offset(1, 0) = newData
newDataRng.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub