List Disctinct Strings from Range of Cells
 

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

List Disctinct Strings from Range of Cells
 

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"golddave" wrote:

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

List Disctinct Strings from Range of Cells
 

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

Luke M wrote:
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.
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

List Disctinct Strings from Range of Cells
 

Take a look at Debra Dalgleish's site he

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

On Sep 16, 7:45*pm, "golddave" <u54833@uwe wrote:
I've tried Advanced Filter several different ways and have not been able to
get it to work.



Luke M wrote:
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.
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- Hide quoted text -

- Show quoted text -

List Disctinct Strings from Range of Cells
 

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

Pete_UK wrote:
Take a look at Debra Dalgleish's site he

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 -

List Disctinct Strings from Range of Cells
 

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" <u54833@uwe wrote in message news:9c37205cf55b6@uwe...
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

List Disctinct Strings from Range of Cells
 

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.

Pete_UK wrote:
Take a look at Debra Dalgleish's site he

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 -

List Disctinct Strings from Range of Cells
 

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

On Sep 17, 12:54*am, "golddave" <u54833@uwe wrote:
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.



Pete_UK wrote:
Take a look at Debra Dalgleish's site he

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 -- Hide quoted text -

- Show quoted text -

List Disctinct Strings from Range of Cells
 

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

Pete_UK wrote:
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 -

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

On Wed, 16 Sep 2009 18:02:16 GMT, "golddave" <u54833@uwe wrote:

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

List Disctinct Strings from Range of Cells
 

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.

Ashish Mathur wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

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

Ron Rosenfeld wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

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,Sheet 1!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
---
"golddave" wrote:
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?

List Disctinct Strings from Range of Cells
 

On Thu, 17 Sep 2009 00:54:05 GMT, "golddave via OfficeKB.com" <u54833@uwe
wrote:

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

List Disctinct Strings from Range of Cells
 

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

"golddave via OfficeKB.com" <u54833@uwe wrote in message
news:9c3ab23840981@uwe...
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.

Ashish Mathur wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

Requirements are set by clinet. Cannot be changed.

Ron Rosenfeld wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

On Thu, 17 Sep 2009 09:01:47 GMT, "golddave via OfficeKB.com" <u54833@uwe
wrote:

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

List Disctinct Strings from Range of Cells
 

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

Max wrote:
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,Shee t1!A2)1,"",CODE(UPPER(LEFT(Sheet1!A2)))+ROW()/10^10))

In B2
=IF(ISERROR(SMALL(A:A,ROWS($1:1))),"",INDEX(Sheet 1!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.
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?

--
Message posted via http://www.officekb.com

List Disctinct Strings from Range of Cells
 

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" <u54833@uwe wrote in message
news:9c407414dac3b@uwe...
I'm getting what appear to be random numbers from these formulas.

List Disctinct Strings from Range of Cells
 

Column B just gives me random numbers.

Max wrote:
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.
I'm getting what appear to be random numbers from these formulas.

--
Message posted via http://www.officekb.com

List Disctinct Strings from Range of Cells
 

Try this sample construct which proves it beyond words:
http://cjoint.com/?jrpvHasO1Z
AutoList Unique Names in Alpha sort.xls

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"golddave via OfficeKB.com" <u54833@uwe wrote in message
news:9c41001dd9bd6@uwe...
Column B just gives me random numbers.

List Disctinct Strings from Range of Cells
 

I have it working now. Good stuff. I see what you mean about the way it
alphabetizes. Is there a way to get it to alphabetize better?

Max wrote:
Try this sample construct which proves it beyond words:
http://cjoint.com/?jrpvHasO1Z
AutoList Unique Names in Alpha sort.xls

Column B just gives me random numbers.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

List Disctinct Strings from Range of Cells
 

Welcome. Suggest you start a new thread just focusing on the true alphabetic
sort issue. To recollect, what I gave earlier does perform both
functionalities that you seek in one go: List uniques and alpha sort by 1st
letter
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"golddave via OfficeKB.com" <u54833@uwe wrote in message
news:9c4280a746d61@uwe...
I have it working now. Good stuff. I see what you mean about the way it
alphabetizes. Is there a way to get it to alphabetize better?

List Disctinct Strings from Range of Cells
 

Why don u try this. It was in another post. List the distinct strings in one
coloumn on a seperate worksheet . match ur main sheet with all the strings
against this and make it return yes/no depending on the string matches.
Filtering trues will give you only the distinct strings and then do a simple
sort..

http://www.microsoft.com/communities...5-dc6c1c4d0c36


--
Sunir M S




"golddave via OfficeKB.com" wrote:

Requirements are set by clinet. Cannot be changed.

Ron Rosenfeld wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1