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))

	A	B	C	D	E	F
1	Client	Dates		Unique List - at least OR more 2 measures	Max	Min
2	A	14/09/18		B	12/10/17	01/08/16
3	B	01/08/16		C	22/05/17	01/12/15
4	B	12/10/17
5	C	01/12/15
6	C	13/06/16
7	C	22/05/17

<tbody>
</tbody>

 

[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!