If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CJOHNSO92" wrote in message
...
Hi Don,
Thanks. getting a compile error at AutoFilter, not sure why. Also, would
appreciate your recommendation on best way to get the macro into the file
each month...maybe have a template file with the macro and copy the data
in?
I won't be the one running this monthly, so trying to make as easy as
possible for the person who will be.
Thanks,
Carol

"Don Guillett" wrote:

One way. Assumes 3 columns
Sub getstudentstatus()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:C" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
On Error Resume Next
For i = lr To 2 Step -3
Cells(i, 4) = "In Progress"
If Application.CountIf(Range(Cells(i - 2, 3), _
Cells(i, 3)), "Completed") = 3 Then Cells(i, 4) = "Completed"
If Application.CountIf(Range(Cells(i - 2, 3), _
Cells(i, 3)), "Not Started") = 3 Then Cells(i, 4) = "Not Started"
Next i
With Range("A1:D1")
..AutoFilter
..AutoFilter Field:=4, Criteria1:="<"
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CJOHNSO92" wrote in message
...
Greetings,
I am trying to combine rows of data for e-learning students and then
determine certification status; here€„¢s an extract as an example:

Smith Module 1 Completed
Jones Module 1 Completed
Doe Module 1 Not Started
Smith Module 2 Completed
Jones Module 2 Completed
Doe Module 2 Not Started
Smith Module 3 Completed
Jones Module 3 In Progress
Doe Module 3 Not Started

First I€„¢d like to sort by Last Name. Once sorted I need status
€€œ if
all 3
modules completed, status='certified'. If only 1 or 2 have been
completed,
status='in progress', if none have been started, status='not started'.
Ultimate goal is:
Smith Completed
Jones In Progress
Doe Not Started
File will be new each month (download from another system) and number
of
rows will vary.
Many thanks for your help!

.