Excel Data|Filter|Advanced 1

[pdsterling]

One really hates to complain, but . . . I have a small project where I was trying to combine three spreadsheets into one worksheet of unique values.

I have a book on tips and tricks which mentioned Data|Filter|Advanced and I tried six ways to the moon to emulate the examples in to book, but the filtered result always comes up with every line intact! talk about going around in circles. FWIW I am still on Excel 2002. any advice would be gratefully appreciated.

Regards,

P D Sterling
Florida NY

 

[SkipVought]

Hi,

Would you like a vague, hypothetical tip, like 'check out Excel Help', or something more specific and concrete: but that would require a sufficient amount of specific and concrete information?

That is my advice.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdsterling]

I appreciate Skip's post, and understand that my query was pretty vague. I'm not certain how to provide more information to the group.

I had three different worksheets, archived from different projects. one had 3000 lines, one had 6000 lines and one had 11,000 lines, basically just lists of email addresses. an individual might be included in those 19,000 lines one time, two times, or three times, but my goal is to have a new combined list of unique email addresses (no other information).

I have carefully emulated the Excel Help information, plus referring to a book of tips and tricks, to the very best of my ability, but the filtered data contains exactly what the input is, no reduction whatsoever. I knew how to do this in dBase V a long time ago, however have not had to use dBase in a long time, and now have a problem getting it installed on my new computer. I am trying very hard to learn Access, but without success.

am hoping to attend Adult Education or similar, but that is another topic.

Regards,

P D Sterling
Florida NY

 

[SkipVought]

It would be helpful to know the structure of each sheet's tables.

A brief copy and paste sample of each would be appropriate.

A table illustrating the result, consistent with the copy and paste examples also.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdsterling]

I guess I did not know I could copy and paste. Here is a filet of file:

Criteria
FALSE

SOURCE EMAIL
P6 maumau29_@hotmail.com
P1 maurice_casillas@hotmail.com
P1 mauricegladys@yahoo.fr
P1 mauricegladys@yahoo.fr
P6 mauricegladys@yahoo.fr
P1 mauricejus@aol.com
P1 mauricioxxx2003@hotmail.com
P3 mauricioxxx2003@hotmail.com
P6 mauricioxxx2003@hotmail.com
P1 mauriliomartins2000@gmail.com
P3 mauriliomartins2000@gmail.com
P6 mauritius.dodo@hotmail.fr
P1 maurycarey@yahoo.com
P1 maurycarey@yahoo.com
P6 maurycarey@yahoo.com
P1 mauvs21@gmail.com
P3 mauvs21@gmail.com
P6 mauvs21@gmail.com
P1 mavacho@hotmail.com
P3 maverickstar@live.co.uk
P3 maverickstar@live.co.uk
P6 maverickstar@live.co.uk


I am seeking unique records only, regardless of source code. I can't make it do what it did before, which was nothing, except copy the entire input, so I am even more frustrated!

regards,
P D Sterling

Regards,

P D Sterling
Florida NY

 

[SkipVought]

Please post what your expected result would be, based on the posted example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdsterling]

expected result of the previous input would look like this:

SOURCE EMAIL
P6 maumau29_@hotmail.com
P1 maurice_casillas@hotmail.com
P1 mauricegladys@yahoo.fr
P1 mauricejus@aol.com
P1 mauricioxxx2003@hotmail.com
P1 mauriliomartins2000@gmail.com
P6 mauritius.dodo@hotmail.fr
P1 maurycarey@yahoo.com
P1 mauvs21@gmail.com
P1 mavacho@hotmail.com
P3 maverickstar@live.co.uk

thank you for taking an interest in my problem

P D Sterling

Regards,

P D Sterling
Florida NY

 

[SkipVought]

I am seeking unique records only, [red]regardless[/red] of source code.

Then WHY include source code as it, by your definition, is [red]irrelevent[/red].

The result is simply a unique list of eMail addresses, which in Excel 2007+ can be accomplished via, Data > Data Tools > Remove Duplicates

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pdsterling]

ah, ha! well, some day I will be forced to upgrade ;-) thanks for giving me a concrete answer!!

regards,

P D Sterling

Regards,

P D Sterling
Florida NY

 

[SkipVought]

There is, in Excel 2003 Advanced Filter, a UNIQUE LIST feature -- No Criteria required!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

just make sure that when you use Data>Filter>Advanced Filter that the selection range is only the email address column

the reason you may be getting all the lines back rather tha just unique email addresses could be that the advanced filter data set may include your "source" reference which would make every line unique even if the email address is duplicated

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question