Managing client data using dates

[iotapsi321]

Good evening. I have had a wonderful support with this forum before and thought I would give it another try. I have 10k+ rows of data that includes a client name, score on a specific measure, and the date when the measure was taken. Some clients will only take the measure once, whereas others will take it multiple times. I need data in which clients have completed at least 2 measures, and for clients who have completed more than 2 measures, I need those clients first and last measure. I included an example below for clarity. So I do not need to retain the data for Client A, but I would need the data for Clients B & C. For Client C, however, I only need the data from 12/01/2015 & 5/22/2017 (the oldest and newest). Does anyone know how I can tackle this problem. Thank you for your time!

Example:

Client Measure Score Date Taken
A 5 9/14/2018
B 2 8/01/2016
B 6 10/12/2017
C 1 12/01/2015
C 4 6/13/2016
C 3 5/22/2017

 

[admiral100]

Hi,

Try this : Ctrl + Shift +Enter NOT just Enter

D2 =IFERROR(INDEX($A$2:$A$7,MATCH(0,IF(--(COUNTIF($A$2:$A$7,$A$2:$A$7)>1),COUNTIF($D$1:D1,$A$2:$A$7)),0)),"")

E2 =MAX(IF(--(D2=$A$2:$A$7),$B$2:$B$7))

F2 =MIN(IF(--(D2=$A$2:$A$7),$B$2:$B$7))


[TABLE="width: 702"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client[/TD]
[TD]Dates[/TD]
[TD][/TD]
[TD]Unique List - at least OR more 2 measures[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]14/09/18[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]12/10/17[/TD]
[TD]01/08/16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]01/08/16[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]22/05/17[/TD]
[TD]01/12/15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]12/10/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C[/TD]
[TD]01/12/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C[/TD]
[TD]13/06/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]C[/TD]
[TD]22/05/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[iotapsi321]

Thank you for your prompt response! I'm afraid that I couldn't quite apply the directions. With more than 10,000 rows I'm not sure how I can apply the formulas or rearrange the data. I'm somewhat of a novice with excel, as my company can't afford the other data management programs that I am used to. Any help you or anyone can provide will be greatly appreciated!