Combine rows of data and use if/then with results

  • Thread starter Thread starter CJOHNSO92
  • Start date Start date
C

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!
 
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
 
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.
 
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
 
Thanks, AB. trying this next

AB said:
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.





.
Click to expand...
 
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.
 
This code of Don Guillett (has 2 dots):

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

Should be changed to (1 dot):

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



hth,
 
Back
Top