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 |