Another Index Match question (I think)

[TyeReece]

My worksheet has 3 columns. A person, a date and a rating (1 to 5). People may have various number of ratings and the data are sorted by ID and date. I need to find and keep any records for a person that are after the most recent rating >3. Any help is appreciated.

 

[J.Ty.]

Can you give an example of your data and (crucially) of the desired result of the computation?

J.Ty.

 

[TyeReece]

Attached is a link to my data. I have separated each ID with a blank row to make it easier to view but my thought is that there is a formula I can put in cell D1 that I can auto fill down. What I want is to keep all of the records for an ID going back in time (data sorted by ID and Date in descending order) until there is a value >3 in column C for an ID. Does this make sense?

https://www.dropbox.com/s/p53g4hhjdy7da4a/Level 3 in Residential.xlsx?dl=0

 

[J.Ty.]

Yes, I know now what you mean. I will make it today.

J.Ty.

 

[J.Ty.]

Sorry, one more question:
what about this record:

323946

9/10/2002

1

del

<tbody>
</tbody>

This is the earliest assgnment lower than 3 for this child.

J.Ty.

 

[TyeReece]

Thanks so much. Once there is a record >3, everything before it I don't want. Put another way, all IDs most recent LOC will be 3 or lower. I want to go back in time, checking each LOC until there is one greater than 3.

 

[J.Ty.]

What should I do if the first record for a particular ID is already >3? Should I go ahead and remove all records for that ID?

J.Ty.

 

[TyeReece]

Per the query design, the first record will always be 3 or less. But for using for other applications, yes it would remove all the records for that ID.

 

[J.Ty.]

Here it is.
Instruction:
Coulmns A-D - your data
Column E - computing keep/del from the records
Columns F-G - help computations
Columns H-J - the records to be kept

Indeed you may use only column E from my solution, create a filter on it and let it hide all records to be deleted, saving on computations carried out in columns F-J.

J.Ty.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Id_Ind	Date Child First Assigned LOC 3 or Lower	Loc			0		Id_Ind	Date Child First Assigned LOC 3 or Lower	Loc
2	222041	1/6/2016	3	keep	keep	1	1	222041	1/6/2016	3
3	222041	10/2/2015	3	keep	keep	2	2	222041	10/2/2015	3
4	222041	2/9/2015	4	del	del	2	8	240575	3/15/2016	3
5	222041	2/6/2015	3	del	del	2	9	250721	12/29/2015	3
6	222041	3/4/2014	3	del	del	2	10	250721	9/29/2015	3
7	222041	12/4/2013	3	del	del	2	11	250721	6/24/2015	3
8	222041	6/5/2013	4	del	del	2	12	250721	4/1/2015	3
9	240575	3/15/2016	3	keep	keep	3	28	308141	1/14/2016	3
10	250721	12/29/2015	3	keep	keep	4	30	323946	3/10/2016	3
11	250721	9/29/2015	3	keep	keep	5	31	323946	12/3/2015	3
12	250721	6/24/2015	3	keep	keep	6	37	364557	1/21/2016	3
13	250721	4/1/2015	3	keep	keep	7	#N/A
14	250721	9/24/2014	4	del	del	7	#N/A
15	250721	4/16/2014	4	del	del	7	#N/A
16	250721	1/14/2014	4	del	del	7	#N/A
17	250721	10/16/2013	5	del	del	7	#N/A
18	250721	7/8/2013	5	del	del	7	#N/A
19	250721	4/12/2013	4	del	del	7	#N/A
20	250721	1/16/2013	4	del	del	7	#N/A
21	250721	10/19/2012	4	del	del	7	#N/A
22	250721	7/19/2012	4	del	del	7	#N/A
23	250721	4/20/2012	5	del	del	7	#N/A
24	250721	1/23/2012	5	del	del	7	#N/A
25	250721	8/11/2011	5	del	del	7	#N/A
26	250721	2/9/2011	4	del	del	7	#N/A
27	250721	11/8/2010	4	del	del	7	#N/A
28	250721	5/10/2010	5	del	del	7	#N/A
29	308141	1/14/2016	3	keep	keep	8	#N/A
30	308141	7/14/2015	5	del	del	8	#N/A
31	323946	3/10/2016	3	keep	keep	9	#N/A
32	323946	12/3/2015	3	keep	keep	10	#N/A
33	323946	9/3/2015	4	del	del	10	#N/A
34	323946	6/5/2015	4	del	del	10	#N/A
35	323946	3/10/2015	4	del	del	10	#N/A
36	323946	9/11/2014	4	del	del	10	#N/A
37	323946	9/10/2002	1	del	del	10	#N/A
38	364557	1/21/2016	3	keep	keep	11	#N/A
39	364557	7/23/2015	4	del	del	11	#N/A
40	364557	1/15/2013	4	del	del	11	#N/A
Sheet2

Cell Formulas
Range	Formula
E2	=IF(C2>3,"del","keep")
E3	=IF(OR(AND(A3=A2,E2="del"),C3>3),"del","keep")
F2	=IF(E2="keep",F1+1,F1)
F3	=IF(E3="keep",F2+1,F2)
G2	=MATCH(ROW()-1,$F$2:$F$40,0)
G3	=MATCH(ROW()-1,$F$2:$F$40,0)
H2	=IFERROR(INDEX(A$2:A$40,$G2),"")
H3	=IFERROR(INDEX(A$2:A$40,$G3),"")
I2	=IFERROR(INDEX(B$2:B$40,$G2),"")
I3	=IFERROR(INDEX(B$2:B$40,$G3),"")
J2	=IFERROR(INDEX(C$2:C$40,$G2),"")
J3	=IFERROR(INDEX(C$2:C$40,$G3),"")

 

[J.Ty.]

P.S.
Note that formulas in E2 and E3 are different. Number 0 in F1 is necessary.

J.Ty.