Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

Autofilter Special

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dk - 26 Mar 2008 16:29 GMT
We need to autofilter  & Copy only records that contain "true" after false
not  all the "true" records?
also limit the cells to information not to 65000 etc
dk - 28 Mar 2008 02:07 GMT
we are attaching a file which we want to filer out the 1 true after false
how can it be done
EGmr 01-DafHaY R Mos M Weiss TRUE
EGmr 01-DafHaY R Mos M Weiss TRUE
EGmr 01-DafHaY R Mos M Weiss TRUE
EGmr 01-DafHaY R Mos M Weiss TRUE
EGmr 01-DafHaY R Mos M Weiss FALSE
EGmr 01-DafHaY R Mos 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

> We need to autofilter  & Copy only records that contain "true" after false
> not  all the "true" records?
> also limit the cells to information not to 65000 etc
Max - 28 Mar 2008 08:36 GMT
Let's back it up a bit and understand carefully what's happening ..

For easy reference, I attach a link to a sample showing your posted data
with the earlier expression that I gave you implemented:

http://www.freefilehosting.net/download/3e988
Flagging Duplicates.xls

In the sample, the unique items are defined based on the fields in cols A to
C (that's what I gathered you specified earlier in your original thread). The
formula in D2**, copied down will flag uniques as FALSE, duplicates as TRUE.
Note that the source data need not be sorted for the flagging to work.

**In D2, copied down:
=IF(COUNTA(A2:C2)<3,"",SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2)*(C$2:C2=C2))>1)

To reconfirm our understanding:  
Uniques = 1st occurrence (FALSE)
Duplicates = 2nd occurrence onwards (TRUE)

And, any source lines with incomplete data for flagging (there must be data
in all 3 cols A to C) will return as blanks: "". This precaution is taken
care of by the front IF part in the expression: IF(COUNTA(A2:C2)<3,"",...

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>  we are attaching a file which we want to filer out the 1 true after false
> how can it be done
[quoted text clipped - 11 lines]
> > not  all the "true" records?
> > also limit the cells to information not to 65000 etc

Rate this thread:






 
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.