 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Remove 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 > 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  SignatureMax 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 > > 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 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 > > 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 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) SignatureMax 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 > > .. 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 > 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) [quoted text clipped - 15 lines] > > > > .. 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 SignatureMax 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 > 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) [quoted text clipped - 15 lines] > > > > .. also if we don't want to delete only copy the filtered to other location waiting for help > 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 [quoted text clipped - 19 lines] > > > > > > .. 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. SignatureMax 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 > > .. what happens if we want to add another 2 columns to filter > [quoted text clipped - 18 lines] > > I'm clueless. Suggest you put this in as a fresh new posting. I'm still clueless. Suggest you put this scenario in as a **fresh new** posting. SignatureMax 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 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? > I'm still clueless. Suggest you put this scenario 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 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? SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > 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 > 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 [quoted text clipped - 7 lines] > > in > > the righ row it's 1 row ahead? See response in your new post. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik ---
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.