Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combine rows and sum data with the same id | Excel Discussion (Misc queries) | |||
combine multiple rows of data into one row. | Excel Programming | |||
combine rows of data to one row...tough one | Excel Programming | |||
How to combine data and delete rows | Excel Programming | |||
Combine data rows in Pie Chart | Charts and Charting in Excel |