Formula challenge - How to write a formula that returns non-duplicate items from a list?

[mfexcel]

Hi all experts,
I am wondering if I could create a dynamic name range that returns only non-duplicate items from a list?

e.g. in cell A1:A6, I have
Apple
Apple
Apple
Banana
Grape
Grape

I want to create a dynamic name range that returns only Apply,Banana,Grape.

Is it possible?

Any hints are appreciate!

 

[markmzz]

Hi Markmzz,
with your formula, i get only "Apple"...

Hi Mfexcel,

Here the formula work like you can see in the post #7.

Could you post the formula that you used?

Did you try with the layout of the post #7?

Markmzz

Hi Mfexcel,

Did you see the post #15 above?

Markmzz

 

[mfexcel]

Hi Markmzz,
Interestingly, it works well in PC but not in Mac...(really have no idea)
Thanks for your nice formula. Impressed by your trick of using +(A2:A9="").

Hi Mfexcel,

Did you see the post #15 above?

Markmzz

 

[Aladin Akyurek]

Array formulas should work on Mac too. Interestingly, you skipped post #20. No comment on that?

 

[markmzz]

Hi Markmzz,
Interestingly, it works well in PC but not in Mac...(really have no idea)
Thanks for your nice formula. Impressed by your trick of using +(A2:A9="").

Hi Mfexcel,

Sorry, but I don't have a Mac to do some tests.

Lets wait for another user that have both (Excel and Mac).

Markmzz