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!

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("A11")
..AutoFilter
..AutoFilter Field:=4, Criteria1:="<>"
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"CJOHNSO92" <(E-Mail Removed)> wrote in message
news:CB62E36C-3F1D-422B-8D48-(E-Mail Removed)...
> 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!

A 'no code' solution:
If the Status establishing algorithm is as you describe, then you can
use this formula (enter in the first row and fill down):

=CHOOSE(IF(SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Not Started"))
=3;1;SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Completed"))+1);"Not
Started";"In Progress";"In Progress";"Certified")

This assumes that your data are in range A1:A9. Also - i'm using ';'
as list separator - in case you use comma ',' - then you need to
replace my ';' with ','.

This would mark all the individuals (even without sorting the list)
based on this logic:
- if all 3 modules for the last name are 'Not Started', then marked as
'Not Started'
- if all 3 modules for the last name are 'Completed', then marked as
'Certified'
- all the rest would be marked as 'In Progress'

Then, if you need to see every indivudual only once - you can filter
out the Unique values by using advanced filter.

A.



On 4 Dec, 16:31, CJOHNSO92 <CJOHNS...@discussions.microsoft.com>
wrote:
> 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!

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("A11")
> ..AutoFilter
> ..AutoFilter Field:=4, Criteria1:="<>"
> End With
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "CJOHNSO92" <(E-Mail Removed)> wrote in message
> news:CB62E36C-3F1D-422B-8D48-(E-Mail Removed)...
> > 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!
>
> .
>

Thanks, AB. trying this next

"AB" wrote:

> A 'no code' solution:
> If the Status establishing algorithm is as you describe, then you can
> use this formula (enter in the first row and fill down):
>
> =CHOOSE(IF(SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Not Started"))
> =3;1;SUMPRODUCT((($A$1:$A$9)=A1)*($C$1:$C$9="Completed"))+1);"Not
> Started";"In Progress";"In Progress";"Certified")
>
> This assumes that your data are in range A1:A9. Also - i'm using ';'
> as list separator - in case you use comma ',' - then you need to
> replace my ';' with ','.
>
> This would mark all the individuals (even without sorting the list)
> based on this logic:
> - if all 3 modules for the last name are 'Not Started', then marked as
> 'Not Started'
> - if all 3 modules for the last name are 'Completed', then marked as
> 'Certified'
> - all the rest would be marked as 'In Progress'
>
> Then, if you need to see every indivudual only once - you can filter
> out the Unique values by using advanced filter.
>
> A.
>
>
>
> On 4 Dec, 16:31, CJOHNSO92 <CJOHNS...@discussions.microsoft.com>
> wrote:
> > 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!
>
> .
>

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
(E-Mail Removed)
"CJOHNSO92" <(E-Mail Removed)> wrote in message
news:6D9D9184-F671-4D51-B1C1-(E-Mail Removed)...
> 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("A11")
>> ..AutoFilter
>> ..AutoFilter Field:=4, Criteria1:="<>"
>> End With
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "CJOHNSO92" <(E-Mail Removed)> wrote in message
>> news:CB62E36C-3F1D-422B-8D48-(E-Mail Removed)...
>> > 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!
>>
>> .
>>

This code of Don Guillett (has 2 dots):

With Range("A11")
...AutoFilter
...AutoFilter Field:=4, Criteria1:="<>"
End With

Should be changed to (1 dot):

With Range("A11")
.AutoFilter
.AutoFilter Field:=4, Criteria1:="<>"
End With



hth,
--
Data Hog


"CJOHNSO92" wrote:

> 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
>