List Disctinct Strings from Range of Cells

[golddave]

I have a spreadsheet where Sheet A contains names (including duplicates) in a
range. I'd like Sheet 2 to list the distinct names in alphabetical order.
Any ideas?

Thanks,
Dave

 

[Luke M]

You can use Data - Filter - Advanced Filer to copy unique values. Note that
you will need to use a named range in order to copy from one sheet to
another. Once copied, simply sort the data.

If you wish this to be automated, I would recommend recording a macro of the
previous actions. You could then either link this to a command button, or a
worksheet_event, depending on your needs.

 

[golddave]

I've tried Advanced Filter several different ways and have not been able to
get it to work.

 

[Pete_UK]

Take a look at Debra Dalgleish's site here:

http://www.contextures.com/xladvfilter01.html#FilterUR

She shows how you can use Advanced Filter to get a list of unique
values.

Hope this helps.

Pete

 

[golddave]

Advanced Filter will not work in my case because my range covers several
columns (not just rows).

Take a look at Debra Dalgleish's site here:

http://www.contextures.com/xladvfilter01.html#FilterUR

She shows how you can use Advanced Filter to get a list of unique
values.

Hope this helps.

Pete

I've tried Advanced Filter several different ways and have not been able to
get it to work.

[quoted text clipped - 14 lines]

- Show quoted text -

 

[Ashish Mathur]

Hi,

You could be on any cell in sheet2. Go to Data > Filter > Advanced Filter.
In the action section, select copy to another location. In the list range,
select the range on sheet1. Leave the criteria range blank and in the copy
to box, select the active cell. Check the box for unique records only. Now
click on OK. This will extract the unique records.

You may now sort the data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[golddave]

I also need it to dynamically update since it's going to be used by non-
technical people who can't be expected to run the filter mechanism.

Take a look at Debra Dalgleish's site here:

http://www.contextures.com/xladvfilter01.html#FilterUR

She shows how you can use Advanced Filter to get a list of unique
values.

Hope this helps.

Pete

I've tried Advanced Filter several different ways and have not been able to
get it to work.

[quoted text clipped - 14 lines]

- Show quoted text -

 

[Pete_UK]

Yes, but Luke advised you earlier that you can record a macro while
you do it once, assign a button (or hot-key) to that macro, and then
just re-run the macro when you need to.

Hope this helps.

Pete

I also need it to dynamically update since it's going to be used by non-
technical people who can't be expected to run the filter mechanism.

Take a look at Debra Dalgleish's site here:

She shows how you can use Advanced Filter to get a list of unique
values.

Hope this helps.

Pete

I've tried Advanced Filter several different ways and have not been able to
get it to work.

[quoted text clipped - 14 lines]

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[golddave via OfficeKB.com]

I cannot use a macro. Client does not allow macros as it is a vector for
viruses.

Yes, but Luke advised you earlier that you can record a macro while
you do it once, assign a button (or hot-key) to that macro, and then
just re-run the macro when you need to.

Hope this helps.

Pete

I also need it to dynamically update since it's going to be used by non-
technical people who can't be expected to run the filter mechanism.

[quoted text clipped - 17 lines]

- Show quoted text -

 

[Ron Rosenfeld]

I have a spreadsheet where Sheet A contains names (including duplicates) in a
range. I'd like Sheet 2 to list the distinct names in alphabetical order.
Any ideas?

Thanks,
Dave

If you can use an add-in, you could download and install Longre's free
morefunc.xll add-in (Google for a download site), and then use his UNIQUEVALUES
function.
--ron

 

[golddave via OfficeKB.com]

This will not work for my purposes. The Advanced Filter considers every row
to be a record and so filters by column to return the row. My sheet is a
seating chart where every cell is a record so I can't filter this way.

Hi,

You could be on any cell in sheet2. Go to Data > Filter > Advanced Filter.
In the action section, select copy to another location. In the list range,
select the range on sheet1. Leave the criteria range blank and in the copy
to box, select the active cell. Check the box for unique records only. Now
click on OK. This will extract the unique records.

You may now sort the data

I have a spreadsheet where Sheet A contains names (including duplicates)
in a

[quoted text clipped - 3 lines]

Thanks,
Dave

 

[golddave via OfficeKB.com]

I tried that dll once and when I uninstalled it the system it was on got
messed up.

 

[Max]

One formulas play which will dynamically list the unique names, sorted
alphabetically by the leftmost letter (its not a true alphabetic sort, but
might suffice for your purpose). The sort criteria is set to make no
distinction between lower/upper case names

Source names assumed listed in Sheet1, in A2 down
In Sheet2,
In A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet1!A2)>1,"",CODE(UPPER(LEFT(Sheet1!A2)))+ROW()/10^10))

In B2
=IF(ISERROR(SMALL(A:A,ROWS($1:1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(A:A,ROWS($1:1)),A:A,0)))
Copy A2:B2 down to cover the max expected extent of source data, say down to
B100. Minimize/hide away the criteria col A. Col B returns the required list
of uniques, sorted in alpha sequence by the leftmost letter (case
insensitive) as mentioned above. Unique names with the same leftmost letter
will be listed in the relative order that they appear within the source. Any
good? Hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Ron Rosenfeld]

I tried that dll once and when I uninstalled it the system it was on got
messed up.

So far you have told us that:
cannot use Advanced Filter because of the shape of your data.
cannot use Advanced Filter because your users cannot run the filter.
require dynamic updating.
Client does not allow macros.
Will not use morefunc.xll because a system got messed up when you
uninstalled it.

I've installed and uninstalled morefunc.xll -- which, by the way, is an "XLL"
and not a "DLL" as you wrote above, with nary a problem. There are some
features which do not work in Excel 2007, but the UNIQUEVALUES function does
work.

I think you will have to modify your requirements.

The morefunc.xll add-in will surely do what you want. But if that is not
acceptable, you will probably need to either develop a secure way of using a
macro that satisfies your Client, or find another tool.
--ron

 

[Ashish Mathur]

Hi,
If you wish, you may mail the file to me at ask(at)ashishmathur(dot)com.
Please explain the problem very clearly and give before/after examples.
Also, please ensure that the file size is not large (kindly remove redundant
information).

Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

This will not work for my purposes. The Advanced Filter considers every
row
to be a record and so filters by column to return the row. My sheet is a
seating chart where every cell is a record so I can't filter this way.

Hi,

You could be on any cell in sheet2. Go to Data > Filter > Advanced
Filter.
In the action section, select copy to another location. In the list
range,
select the range on sheet1. Leave the criteria range blank and in the
copy
to box, select the active cell. Check the box for unique records only.
Now
click on OK. This will extract the unique records.

You may now sort the data

I have a spreadsheet where Sheet A contains names (including duplicates)
in a

[quoted text clipped - 3 lines]

Thanks,
Dave

 

[golddave via OfficeKB.com]

Requirements are set by clinet. Cannot be changed.

 

[Ron Rosenfeld]

Requirements are set by clinet. Cannot be changed.

Good luck. Sometimes clients set conditions that cannot be met, and you have
to educate them.
--ron

 

[golddave via OfficeKB.com]

I'm getting what appear to be random numbers from these formulas.

 

[Max]

In Sheet2, the dynamic results should appear in col B.
(Col A is the criteria, hide it away, as mentioned)
Give it another try. I tested it fine over here. It dhould work over there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[golddave via OfficeKB.com]

Column B just gives me random numbers.

In Sheet2, the dynamic results should appear in col B.
(Col A is the criteria, hide it away, as mentioned)
Give it another try. I tested it fine over here. It dhould work over there.