List Unique Values in a Named Range

[tomlancaster]

Help! I am trying to list the unique values in a named range. Whilst the below formula works, I cannot seem to get it to look at named ranges in a separate sheet?

Does anyone know how I can modify this formula to list the named ranges in a separate sheet in the active sheet?

=IF(ISERR(SMALL(IF(FREQUENCY(MATCH(Name,Name,0),MATCH(Name,Name,0))>0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(FREQUENCY(MATCH(Name,Name,0),MATCH(Name,Name,0))>0,ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1))))

Commit with CTRL+SHIFT+ENTER

Thanks

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[tomlancaster]

Thanks for your reply - I've made a workbook with the two separate sheets.

I have defined named ranged for 3 columns in the TEAM sheet

E.g. "AutoGCB5" is sheet TEAM I:I

The GCB Sheet needs to look at the TEAM sheet in columns G H and I and list the distinct values.

E.g. AutoGCB5 has

Tom
Tom
Tim
Tim
Tim
Fred
Tom
Bob
Frank

But I want to show in GCB sheet

Tom
Tim
Fred
Bob
Frank

The formula i have in GCB sheet works if its in the same sheet, but not in a separate one.

I cannot use Advance Filter because I plan to share this workbook.

I'd love some help on this one ;-)

[NBVC]

For one thing, with Array formulas, you can't have full column references..and your named ranges have full column references.

The other thing is that you have many blanks in the Team sheet also, which have to be addressed...

The best way would be to redefine your Named ranges using dynamically named ranges..

So change the AutoGCB5 range to refer to:

Please Login or Register  to view this content.

and AutoGCB6 to refer to:\

Please Login or Register  to view this content.

and AutoGCB6 to refere to:

Please Login or Register  to view this content.

Then update the formula to eliminate the need to double evaluate the hefty array formula, in A2 of GCB sheet:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down...

use same formulas in B2 and C2, adjusting what needs to be adjusted.

Another thing, try to reduce the number of cells you copy down too.. as, again, array formulas are hefty processor hogs....

[tomlancaster]

Thank you so much for your help - it is very much appreciated