Removing duplicates (both) only keeping unique data

[PondWaters]

I was able to get some help from this forum earlier, and am hoping for the same.


I have files that come in twice a month with around 135k rows of data each, of the 270k that come in, I would say around 265 to 268k are complete duplicates. Is there a way for me to completely remove BOTH duplicates and only keep unique data? Also, I am matching on fields 2, 7 and 8

Sheet1

*	A	B	C	D	E	F	G	H
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
2	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
3	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
4	147258369	878965412-1	Jones	Fred	20150501	20150531	0	B
5	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
6	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
7	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:71px;"><col style="width:71px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The second file later in the month would come in like this:

Sheet1

*	A	B	C	D	E	F	G	H
9	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
10	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
11	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
12	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
13	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
14	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
15	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B
16	324257245	32584415-7	Blue	James	20150501	20150531	$23.00	A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:71px;"><col style="width:71px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I would want the following where the duplicates are completely removed:


I know how to remove just one duplicate so Access removes one of the two, but I need both lines removed. I have been searching but most everything i can find is for removing on single fields, I need fields 2, 7 and 8 to be exact matches.

Sheet1

*	A	B	C	D	E	F	G	H
19	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
20	147258369	878965412-1	Jones	Fred	20150501	20150531	0	B
21	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
22	324257245	32584415-7	Blue	James	20150501	20150531	$23.00	A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:77px;"><col style="width:64px;"><col style="width:64px;"><col style="width:71px;"><col style="width:71px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jarjarbingie]

this may not be the most efficient way of doing it, but adding helper columns might help:

(1) I2 = B2&G2&H2 where B2 is Field2, G2 is Field7 and H2 is Field8
(2) J2 = COUNTIF($I$2:$I$14,$I2)

then filter column J for counts of 1

I was able to get some help from this forum earlier, and am hoping for the same.


I have files that come in twice a month with around 135k rows of data each, of the 270k that come in, I would say around 265 to 268k are complete duplicates. Is there a way for me to completely remove BOTH duplicates and only keep unique data? Also, I am matching on fields 2, 7 and 8

Sheet1

*	A	B	C	D	E	F	G	H
1	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
2	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
3	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
4	147258369	878965412-1	Jones	Fred	20150501	20150531	0	B
5	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
6	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
7	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The second file later in the month would come in like this:

Sheet1

*	A	B	C	D	E	F	G	H
9	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
10	123456789	123456789-1	Smith	Bob	20150501	20150531	$12.00	A
11	987654321	963258741-1	Smith	Carol	20150501	20150531	$14.00	A
12	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
13	369258147	8795412-2	Jones	Erica	20150501	20150531	$8.00	C
14	321654987	3251148-1	James	Tom	20150501	20150531	$25.00	C
15	357147159	32584415-6	James	Robert	20150501	20150531	$19.50	B
16	324257245	32584415-7	Blue	James	20150501	20150531	$23.00	A

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


I would want the following where the duplicates are completely removed:


I know how to remove just one duplicate so Access removes one of the two, but I need both lines removed. I have been searching but most everything i can find is for removing on single fields, I need fields 2, 7 and 8 to be exact matches.

Sheet1

*	A	B	C	D	E	F	G	H
19	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
20	147258369	878965412-1	Jones	Fred	20150501	20150531	0	B
21	147258369	878965412-1	Jones	Fred	20150501	20150531	$33.00	B
22	324257245	32584415-7	Blue	James	20150501	20150531	$23.00	A

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[PondWaters]

It sounds like the solution you have is in Excel, I am trying to stay away from Excel due to the size of the files - once I put formulas in on large files Excel seems to slow down immensely, to the point of crashing. Is there a way in Access to do this?

 

[jarjarbingie]

basically, the same logic should apply, concatenate all 3 fields to create an ID, then in another query, do a Group by of that field as well as count, filter for 1..

It sounds like the solution you have is in Excel, I am trying to stay away from Excel due to the size of the files - once I put formulas in on large files Excel seems to slow down immensely, to the point of crashing. Is there a way in Access to do this?

 

[PondWaters]

I am running into another issue with this that I can;t figure out:

I am in design view:

I concatenated all of the fields and that works like a charm.
I have Expr2 as Count([Expr1])

I click totals at the top and have total as Expression. I am expecting that Expr2 will show some 2's since I have a bunch of duplicates. Everything shows as 1. What am I doing wrong? I have the same formula in another spreadsheet - the exception being the name of the document (I even checked SQL view), so I can't figure out why this is not working for me.