Combine rows of data and use if/then with results

[CJOHNSO92]

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!

 

[Don Guillett]

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

 

[AB]

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.

 

[CJOHNSO92]

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

 

[CJOHNSO92]

Thanks, AB. trying this next

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.





.

 

[Don Guillett]

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.

 

[J_Knowles]

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,