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

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

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
---

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

.. 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:
=IF(COUNTA(A2:E2)<5,"",SUMPRODUCT((A$2:A2=A2)*(B$2 :B2=B2)*(C$2:C2=C2)*(D$2:D2=D2)*(E$2:E2=E2))1)
Copy down, rest of steps similar

Also sometimes filter by 3 columns sometime by 5

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

we realized also that the true after the false that we need 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

I'm clueless. Suggest you put this in as a fresh new posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

.. 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:
=IF(COUNTA(A2:E2)<5,"",SUMPRODUCT((A$2:A2=A2)*(B$2 :B2=B2)*(C$2:C2=C2)*(D$2:D2=D2)*(E$2:E2=E2))1)
Copy down, rest of steps similar

Also sometimes filter by 3 columns sometime by 5

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

we realized also that the true after the false that we need 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

I'm clueless. Suggest you put this in as a fresh new posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I'm still clueless. Suggest you put this scenario in as a **fresh new**
posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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?

"Max" wrote:

I'm still clueless. Suggest you put this scenario in as a **fresh new**
posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dk" wrote in message
...
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

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

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

"Max" wrote:

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

See response in your new post.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---