Identify rows that have cells that match criteria in other rows

[bgill]

Good morning! I am using Excel 2010 and have a set of data with over 200000 rows. I need to find all of the rows that have an 18 in the first column where the ID and the # are the same as any row with the same absolute value as the value in the row with 18 in the code. If the person listed has any rows that meet the above criteria I will need to keep all of the rows for that person. If they don't, I can delete all of the rows for that person. For example, I want to keep all of the rows for John but can delete both rows for Nancy. I am fine with a formula that will identify the ones I can keep or delete and then using a filter. Any ideas?

Excel 2010
	A	B	C	D	E	F	G	H
1	CODE	ID	Qtr	#	BYE Date	FULL NAME	Charges/Credits	absolute vlaue
2	18	341548	20142	1234	20140302	John	(5,004.00)	5004.00
3		341548	20142	1234	20140302	John	(5,004.00)	5004.00
4		341548	20134	1234	20140302	John	5,004.00	5004.00
5	18	1573024	20134	2345	20130714	Nancy	550.00	550.00
6		1573024	20134	2345	20130714	Nancy	1,650.00	1650.00
7		2265844	20142	34456	20130818	Sandra	0.04	0.04
8	18	2265844	20142	34456	20130818	Sandra	75.15	75.15
9	18	2659714	20142	34456	20130818	Sandra	42.53	42.53
10		9950055	20142	34456	20130818	Sandra	(0.01)	0.01
11		2942387	20134	5678	20140126	Susan	258.00	258.00
12	18	2942387	20142	5678	20140126	Susan	(4,902.00)	4902.00
13		2942387	20141	5678	20140126	Susan	(4,902.00)	4902.00
14		2942387	20142	5678	20140126	Susan	(4,902.00)	4902.00
15		2942387	20142	5678	20140126	Susan	4,902.00	4902.00
16	18	2339500	20141	7890	20140602	Melanie	64.00	64.00
17		2339500	20141	7890	20140602	Melanie	122.00	122.00
18	18	2827824	20142	9876	20140330	Ray	56.00	56.00
19		2827824	20142	9876	20140330	Ray	(56.00)	56.00
20	18	612261	20134	8765	20101101	Dee	(1,257.60)	1257.60
21		612261	20134	8765	20101101	Dee	(1,257.60)	1257.60
22	18	2172611	20134	8765	20101101	Dee	(694.40)	694.40
23		2172611	20134	8765	20101101	Dee	(694.40)	694.40
24		3054441	20134	7654	20130630	Kelley	(20.00)	20.00
25	18	3054441	20141	7654	20130630	Kelley	(25.00)	25.00
26		3054441	20141	7654	20130630	Kelley	(25.00)	25.00
27	18	1309019	20134	7654	20140601	Alana	3.27	3.27
28		1309019	20134	7654	20140601	Alana	8.19	8.19
Sheet1

 

[Andrew Poulsom]

Try this formula in I2 copied down:

=COUNTIFS(B:B,B2,D:D,D2,H:H,H2)>1

You can then AutoFilter column I for FALSE and delete the visible rows.

 

[bgill]

Thank you Andrew. That is really close to what I need. The only problem is that I need to keep all of the rows for the same person if any of the rows for that person have the matching criteria. For example, I need to keep all of the rows for Susan, 11 - 15, even though row 11 does not have a matching value.

 

[Andrew Poulsom]

How about?

=COUNTIFS(B$2:B$28,B2,D$2:D$28,D2,H$2:H$28,INDEX(H$2:H$28,MATCH(1,INDEX((A$2:A$28=18)*(B$2:B$28=B2)*(D$2:D$28=D2),),FALSE)))>1

 

[bgill]

Brilliant! Thank you very much Andrew. You have just saved me hours and hours and hours of reading 200000 rows of data!

Happy Holidays to you!