Multiple entries/rows of a unique name, and finding the maximum value in another column

[Adufresne]

This will make more sense when you look at the sample of my spreadsheet.

I have hundreds of students that take an online class from me. I get a report (CSV file) from my Learning Management System, Schoology that includes a lot of metrics related to student activity. It's information overload with 1000's of rows of a spreadsheet taken up. The good news is, the information I want is contained in this massive spreadsheet. Bad news is, I don't know how to pull it out.

I'm looking to extract a few things from this enormous CSV sheet. 1) I want the first name, last name, and last login date (column AG) for each student. 2) I want a field that will be populated if a phrase that begins with "I have completed" is found in a column K.

The challenge is, the current CSV file shows ALL activity for each student. For example, Sydney Adams takes up 57 rows of the spreadsheet documenting every single activity they performed (in this case it's 57 activities and 57 rows on the spreadsheet). Sydney completed these activities from 7/2/20 through 7/8/20. All 57 rows have a date of when that activity was performed. I simply want the LATEST date to be extracted out for Sydney Adams. The further challenge of this is the fact there are multiple students. So somehow the formulas/functions created need to know how to stop analyzing the data for Sydney Adams after row 57, and start analyzing data for the next student, Noah Angell (who has 8 rows of activities on the spreadsheet).

In column K, Sydney Adams has a result that starts with "I have completed". I want that cell result to be pulled out.

I'm attaching a sample of my massive spreadsheet (sample includes less than 100 rows). The tabs at the bottom are titled "original file" and "desired output." It's pretty self explanatory, but "original file" is what I'm working with, and the desired output is what the spreadsheet would look like if I had it my way.

I've never used this forum before. I'm very curious to see what kind of help I get. Looking forward to it!

[menem]

If you allow me to use helper columns.

Insert helper columns and enter these formulas.
A2
='Original File'!T2&"_"&'Original File'!S2

B2
='Original File'!T2

C2
='Original File'!S2

D2
='Original File'!AG2

E2
='Original File'!K2

F2 (find number of student)
=IF(COUNTIF(A$1:A2,A2)=1,F1+1,F1)

Next....
H1 ( find total count of students)
=MAX(F:F)-1

I2 ( find row number of last record of each student)
=MATCH(K2,F:F,1)

In output area.
K2 (Student no.)
1 ( and plus 1 in next rows)

L2
=IF($K2<=$H$1,INDEX($B:$B,$I2),"")

M2
=IF($K2<=$H$1,INDEX($C:$C,$I2),"")

N2
=IF($K2<=$H$1,INDEX($D:$D,$I2),"")

O2
=IF($K2<=$H$1,IFERROR(LOOKUP(2,1/((--($A:$A=L2&"_"&M2)*--(LEFT(E:E,16)="I have completed"))),E:E),""),"")
** Note I've use 'ALL' column reference, please adjust as you want.

And helper column (A-F) must equal or greater that original data.

Regards.

[bebo021999]

This is a well-organised database that you can work with.

Using "unique_user_id" in column A, to establish unique list :

A2:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Drag down as far as needed

B2:

Please Login or Register  to view this content.

Copy to C2, change 14 into 13

D2:

Please Login or Register  to view this content.

E2:

Please Login or Register  to view this content.

Drag all down