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" wrote:
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

please explain more detailed
also if we don't want todelete only copy the filtered to other location
thank you

"Max" wrote:

"dk" wrote:
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

"dk" wrote:
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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

.. also if we don't want to delete only copy the filtered to other location

Thank You
what happens if we want to add another 2 columns to filter
Also sometimes filter by 3 columns sometime by 5

"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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

.. also if we don't want to delete only copy the filtered to other location

Can you go back to my previous response & complete step 5 first?
.. click the "Yes" button from where you're reading this response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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

.. also if we don't want to delete only copy the filtered to other location

waiting for help

"dk" wrote:

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

"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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

.. also if we don't want to delete only copy the filtered to other location