How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1874
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Excel 2011 for MAC using Advance Filter

This answer was rated:

I'm using Excel 2011 for MAC and trying to use Advanced Filter and asking it to filter unique records only but it doesn't filter. 

Attachment: 2013-10-31_214410_test.xlsx


The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

how exactly are you trying to filter the data?

Customer:

There are duplicate profile ID's in the first column. I want to return the same list but any duplicates only 1 of the rows will show.

Customer:

ie. Row 2 and 3 both have the same profile ID. I want my new list to only show Row 2 for example

The-PC-Guy :

and regular filters do not work?

Customer:

I filtered by colour but it removes all the unique profile id rows.

Customer:

I tried "remove duplicates" but it removes every row that has a matching profile ID..so in that process row 2 and 3 were removed...which isn't what I want

The-PC-Guy :

ok

The-PC-Guy :

i understand

The-PC-Guy :

filtering isn't going to do it then

The-PC-Guy :

I dont know how it works on mac

The-PC-Guy :

but on windows when I do remove duplicates it does keep each row at least 1x

The-PC-Guy :

so do you want to send the file to me

The-PC-Guy :

have me run it

Customer:

Oh I thought because I put "for MAC" in the subject line, I would get someone who knows excel for mac?

The-PC-Guy :

i know enough to know that its functionality is limited

The-PC-Guy :

i mean if you send the file

The-PC-Guy :

i run it

The-PC-Guy :

then send the file back

Customer:

I did upload the file and it shows as an attachment on the window I'm on

The-PC-Guy :

ok please try again

The-PC-Guy :

because I never got the attachment

Customer:

is it easier for me to email...or attach it here...stand by

Customer:

Did that work?

The-PC-Guy :

yes

Customer:

yeah!

The-PC-Guy :

give me a minute to look at it

Customer:

ok

The-PC-Guy :

let me know if this is what you were looking for

Customer:

Yes that is what result I was looking for...

Customer:

Ok what is the secret?

The-PC-Guy :

secret is being on a PC

Customer:

I walked into that one

The-PC-Guy :

sorry I am not much of a MAC fan for obvious reasons

The-PC-Guy :

but

The-PC-Guy :

they do have their uses

The-PC-Guy :

espicially in graphics

The-PC-Guy :

please rate when ready so I am reimbursed for my time

Customer:

yeah pivot filtering is a bust on excel for mac too...

The-PC-Guy :

not surprising

Customer:

Ok...but what did you do so I can replicate?

The-PC-Guy :

maybe next version will work better

The-PC-Guy :

um not sure how it works on mac

The-PC-Guy :

but i higlighted all columns

Customer:

Ok what did you do on the PC?

The-PC-Guy :

then i went to the data tab

The-PC-Guy :

selected remove duplicates

The-PC-Guy :

it gave me a window that allowed me to select withch columns

The-PC-Guy :

i selected only column A

The-PC-Guy :

then clicked ok

The-PC-Guy :

so if they don't give you the ability to select columns on mac

Customer:

hmmm

The-PC-Guy :

that could be the reason

Customer:

can you stand by...while I try this?

The-PC-Guy :

sure

The-PC-Guy :

also

The-PC-Guy :

make sure to remove any filters

The-PC-Guy :

before doing this

Customer:

Ok, that worked. let me ask you. If I want it to keep the row that has the "2" in it...Speaker order column. Is there a way I can get it to remove the row with 1 in it?

The-PC-Guy :

not easily, remove duplicates just picks whatever is first i think

The-PC-Guy :

it could probably be done with a macro

The-PC-Guy :

but that is very involved, and I am not sure about 2011s ability to run macros well, I know it does support it, but not sure of the extent of its abilities

Customer:

I guess if I re-sort it to have the row with "2" in it be first ... then run the remove duplicates...it should work?

The-PC-Guy :

in theory, its worth a try

The-PC-Guy :

if not working would require a macro

Customer:

yeah...I run the other way when someone says macro...I would need to take a course I think . LOL

The-PC-Guy :

i would create it for an additional fee, as it goes beyond the scope of this question

Customer:

No that is fine....Ok thanks

The-PC-Guy :

anyway, I enabled the rating buttons, now, they are the smileys underneath this chat

The-PC-Guy :

if you change your mind and want a macro

The-PC-Guy :

let me know

Customer:

Ok thanks!

The-PC-Guy and other Microsoft Office Specialists are ready to help you