Combine rows of data and use if/then with results
 

Greetings,
I am trying to combine rows of data for e-learning students and then
determine certification status; heres 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 Id 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!

Combine rows of data and use if/then with results
 

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; heres 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 Id 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!

Combine rows of data and use if/then with results
 

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="Compl eted"))+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
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!

Combine rows of data and use if/then with results
 

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!

.

Combine rows of data and use if/then with results
 

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="Compl eted"))+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
wrote:
Greetings,
I am trying to combine rows of data for e-learning students and then
determine certification status; heres 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 Id 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!

.

Combine rows of data and use if/then with results
 

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!

.

Combine rows of data and use if/then with 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,
--
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