Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

"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
---
  #3   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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

  #4   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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

  #5   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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




  #6   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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


  #7   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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


  #8   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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


  #10   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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




  #11   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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



  #12   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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



  #13   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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




  #14   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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?



  #15   Report Post  
Posted to microsoft.public.excel.newusers
dk dk is offline
external usenet poster
 
Posts: 129
Default Remove Duplicates

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?






  #16   Report Post  
Posted to microsoft.public.excel.newusers
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Remove Duplicates

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to remove duplicates? Lakewoodsale Excel Discussion (Misc queries) 2 January 25th 08 11:31 PM
Merge and remove duplicates? Shanel Excel Worksheet Functions 1 January 23rd 08 11:13 PM
how to remove duplicates ONLY within the same category Landa Excel Discussion (Misc queries) 5 April 26th 07 10:04 AM
Compare columns remove duplicates Tommy Geek Excel Discussion (Misc queries) 1 December 21st 06 07:42 PM
Remove duplicates Tuttamay77 Excel Discussion (Misc queries) 4 May 12th 06 10:56 PM


All times are GMT +1. The time now is 12:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"