Remove Duplicates

  • Thread starter Thread starter dk
  • Start date Start date
D

dk

How can you remove filter records which are the same in 3 to 4 columns only
the ones that have in all 3 columns same wording
 
dk said:
How can you remove filter records which are the same in 3 to 4 columns only
the ones that have in all 3 columns same wording
Click to expand...

Assume the 3 cols are cols C to E
In F2, copied down: =SUMPRODUCT(--MATCH(C2:E2,C2:E2,0))=3
Then apply n autofilter for TRUE in F1, delete these filtered rows

---
 
please explain more detailed
also if we don't want todelete only copy the filtered to other location
thank you
 
ex: column a: is filename b:date modified c:type
only when all 3 matches the exact same
please note this is only example this is a listing of 28,000 rows of data
 
Now that you've clarified your scenario better, try this on a spare copy
1. First, switch the calc mode* to manual (you've got 28k rows)
2. Put this in say, D2:
=IF(COUNTA(A2:C2)<3,"",SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2)*(C$2:C2=C2))>1)
Copy D2 down to the last row of source data in cols A to C. Press F9 to
calc. Select col D, kill the formulas with an in-place: copy n paste special
as values. Switch the calc mode back to automatic.
3. Apply n autofilter D1 for: TRUE
4. Copy the filtered rows > paste special as values/formats into another sheet
5. Post a closure feedback here. Also, click the "Yes" button from where
you're reading this response.

*Click Tools > Options > Calculation tab (options to switch calc mode are
there)
 
Thank You
what happens if we want to add another 2 columns to filter
Also sometimes filter by 3 columns sometime by 5
 
we realized also that the true after the false that weneed because all of
them are only when there is a difference in the cells then the last row
before the change gets the false but the change has a true like all mojaroty
 
waiting for help

dk said:
we realized also that the true after the false that weneed because all of
them are only when there is a difference in the cells then the last row
before the change gets the false but the change has a true like all mojaroty
Click to expand...
 
.. what happens if we want to add another 2 columns to filter

Just extend the criteria in the helper col to suit

Eg if you have 5 cols to check, cols A to E

Put this in say, F2:
Also sometimes filter by 3 columns sometime by 5
Click to expand...

You can always do up the criteria check on 3 cols in one helper col, and
frame up the 5 cols check in another helper col. Then filter separately, etc

I'm clueless. Suggest you put this in as a fresh new posting.

---
 
whats with the problem of true & false the last one before the change gets a
false all others get true we nedd to copy the true after the false
 
we have put in new post already 1 day as autofilter special but no replies so
maybe original idea is accurate because the change of true & false isn't in
the righ row it's 1 row ahead?
 
Maybe it's because your problem description is so terse & vague that nobody
can quite understand what you're talking about. Suggest you elaborate and
illustrate your scenario fully, post the expression that you're using, etc.
Of course you need to be certain yourself what exactly is it that you want
to do. If you yourself are not clear what you want, how do you expect others
to be able to respond?
 
thanks for relying

We copied your code & we are attaching a file with results
EGmr 01-DafHaY R Moshe M Weiss TRUE
EGmr 01-DafHaY R Moshe M Weiss TRUE
EGmr 01-DafHaY R Moshe M Weiss TRUE
EGmr 01-DafHaY R Moshe M Weiss TRUE
EGmr 01-DafHaY R Moshe M Weiss FALSE
EGmr 01-DafHaY R Moshe Gold TRUE

look were the false & true comes up when we filter we need the new name row
not all the true's only the first true after false
 
Back
Top