finding & sorting unqiue names list

Good day everyone,

in this list, i learned that that there is no such thing described as
impossible since i see new propblems solved everyday, i hope you are
ready for a new challenge i.e. at least it is to me.

i have a list of names in A1:A50. the name could be more than one word
length i.e. have spaces. we have a limit of text length to be 35
characters. the list may have duplicate entries. the names are entered
in random order and they are not case sensetive, hence, the original
name list is not sorted. i wish to extract a list of the unique names
from the original list is column A and then, have the extracted list
sorted out in either ascending or descending order. both of original
name list and sorted names are on the same worksheet. the sorted names
shall be in column D1 upto D????

i wish to sort these names either ascending or descending order using
the following manner: -

- find the number of the unique names in range A1:A50 and store it in
cell B1, name this cell as n
- write a formula that extracts unique names in the original list and
place it in range C1:C(1+n) to avoide getting any error message on the
screen. now, this range has a list of unique names, however, not
necessarily sorted.
- in range D1(1+n) i need to sort out the names listed in the range
C1:C(1+n). one way i think, shall run as follows: -
- find the max name in the range C1:C(1+n) and place it in D1.
- define a dynamic range that will have the entries of C1:C(1+n) less
the name previously entered in D1, then, find the max. name in this
dynamic range and place it in D2, repeat the process until we are left
with the minimum value in this range which shall be in cell D(1+n).
what do you think?!

is this really possible,

TIA

Khaldoun


---
Message posted from http://www.ExcelForum.com/

Hi
do you need a formula approach (that is do you need a dynamically
updated list?). If no you may use
'Data - Filter - Advanced Filter'. Check 'Unique entries' and sort the
extracted values manually

If you need a formula approach please post back. This is also possible
but a little bit more complicated


--
Regards
Frank Kabel
Frankfurt, Germany


Good day everyone,

in this list, i learned that that there is no such thing described as
impossible since i see new propblems solved everyday, i hope you are
ready for a new challenge i.e. at least it is to me.

i have a list of names in A1:A50. the name could be more than one
word length i.e. have spaces. we have a limit of text length to be 35
characters. the list may have duplicate entries. the names are
entered in random order and they are not case sensetive, hence, the
original name list is not sorted. i wish to extract a list of the
unique names from the original list is column A and then, have the
extracted list sorted out in either ascending or descending order.
both of original name list and sorted names are on the same
worksheet. the sorted names shall be in column D1 upto D????

i wish to sort these names either ascending or descending order using
the following manner: -

- find the number of the unique names in range A1:A50 and store it in
cell B1, name this cell as n
- write a formula that extracts unique names in the original list and
place it in range C1:C(1+n) to avoide getting any error message on
the
screen. now, this range has a list of unique names, however, not
necessarily sorted.
- in range D1(1+n) i need to sort out the names listed in the range
C1:C(1+n). one way i think, shall run as follows: -
- find the max name in the range C1:C(1+n) and place it in D1.
- define a dynamic range that will have the entries of C1:C(1+n) less
the name previously entered in D1, then, find the max. name in this
dynamic range and place it in D2, repeat the process until we are
left
with the minimum value in this range which shall be in cell D(1+n).
what do you think?!

is this really possible,

TIA

Khaldoun


---
Message posted from http://www.ExcelForum.com/

Dear Frank,

Thanks for such swift response.

i am aiming at doing this via formula, since what i need is a part of
bigger project wherein spreadsheets are used by many other people who
might have varying skill levels.

i appreciate your help, as always, you are one of the shining starts in
the list...

tia

khaldoun


p.s.
i would appreciate your comment on the logic i explained to do the
job. is it ok or is there a more straight forward method?!


---
Message posted from http://www.ExcelForum.com/

you can try...
After selecting 35 or more cells...insert this array formula:
=INDEX(text,MATCH(SMALL(IF(MATCH(text,text,)=ROW(t ext),1)*(COUNTIF(text,"="
&text)+ROWS(text)*NOT(ISTEXT(text))),ROW(text)+ROW S(text)-SUM(1/COUNTIF(text
,text))),COUNTIF(text,"="&text)+ROWS(text)*NOT(IS TEXT(text)),))


text= your data areas
this formula return unique value and sort ascendenting..

--
Ivano Chiappa Gruppo RIO
Iscriviti gratuitamente alla newsletter di IT.OFFICE. Invia una email a
it.office @ mvps.org con oggetto "Richiesta it.office".




"iwtci " ha scritto nel messaggio
...
Good day everyone,

in this list, i learned that that there is no such thing described as
impossible since i see new propblems solved everyday, i hope you are
ready for a new challenge i.e. at least it is to me.

i have a list of names in A1:A50. the name could be more than one word
length i.e. have spaces. we have a limit of text length to be 35
characters. the list may have duplicate entries. the names are entered
in random order and they are not case sensetive, hence, the original
name list is not sorted. i wish to extract a list of the unique names
from the original list is column A and then, have the extracted list
sorted out in either ascending or descending order. both of original
name list and sorted names are on the same worksheet. the sorted names
shall be in column D1 upto D????

i wish to sort these names either ascending or descending order using
the following manner: -

- find the number of the unique names in range A1:A50 and store it in
cell B1, name this cell as n
- write a formula that extracts unique names in the original list and
place it in range C1:C(1+n) to avoide getting any error message on the
screen. now, this range has a list of unique names, however, not
necessarily sorted.
- in range D1(1+n) i need to sort out the names listed in the range
C1:C(1+n). one way i think, shall run as follows: -
- find the max name in the range C1:C(1+n) and place it in D1.
- define a dynamic range that will have the entries of C1:C(1+n) less
the name previously entered in D1, then, find the max. name in this
dynamic range and place it in D2, repeat the process until we are left
with the minimum value in this range which shall be in cell D(1+n).
what do you think?!

is this really possible,

TIA

Khaldoun


---
Message posted from http://www.ExcelForum.com/

Let A2:A17 on Sheet1 house the following sample:

{"List";"damon";"jon";"bob";"chris";"jon";"bob";"" ;"julie";"bob";"brian";"br
ian";"steve";"jon";"ricky";"bacchus"}

List is a label and "" stands for an empty cell.

In B1 enter:

=MATCH(REPT("z",255),A:A)-(CELL("Row",A3)-1)

In B3 enter & copy down:

=IF((A3"")*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT( (A3OFFSET($A$3,0,0,$B$1,1
))+0)+1,"")

In C3 enter & copy down:

=IF(B3"",RANK(B3,B:B,1)+COUNTIF($B$3:B3,B3)-1,"")

In D1 enter:

=MAX(C:C)

In D3 enter & copy down:

=IF(ROW()-ROW(D$3)+1=$D$1,INDEX(A:A,MATCH(ROW()-ROW(D$3)+1,C:C,0)),"")

In D you get a sorted list of distinct names.

"iwtci " wrote in message
...
Good day everyone,

in this list, i learned that that there is no such thing described as
impossible since i see new propblems solved everyday, i hope you are
ready for a new challenge i.e. at least it is to me.

i have a list of names in A1:A50. the name could be more than one word
length i.e. have spaces. we have a limit of text length to be 35
characters. the list may have duplicate entries. the names are entered
in random order and they are not case sensetive, hence, the original
name list is not sorted. i wish to extract a list of the unique names
from the original list is column A and then, have the extracted list
sorted out in either ascending or descending order. both of original
name list and sorted names are on the same worksheet. the sorted names
shall be in column D1 upto D????

i wish to sort these names either ascending or descending order using
the following manner: -

- find the number of the unique names in range A1:A50 and store it in
cell B1, name this cell as n
- write a formula that extracts unique names in the original list and
place it in range C1:C(1+n) to avoide getting any error message on the
screen. now, this range has a list of unique names, however, not
necessarily sorted.
- in range D1(1+n) i need to sort out the names listed in the range
C1:C(1+n). one way i think, shall run as follows: -
- find the max name in the range C1:C(1+n) and place it in D1.
- define a dynamic range that will have the entries of C1:C(1+n) less
the name previously entered in D1, then, find the max. name in this
dynamic range and place it in D2, repeat the process until we are left
with the minimum value in this range which shall be in cell D(1+n).
what do you think?!

is this really possible,

TIA

Khaldoun


---
Message posted from http://www.ExcelForum.com/

"ivano" ha scritto nel messaggio
...
you can try...
After selecting 35 or more cells...insert this array formula:

=INDEX(text,MATCH(SMALL(IF(MATCH(text,text,)=ROW(t ext),1)*(COUNTIF(text,"="

&text)+ROWS(text)*NOT(ISTEXT(text))),ROW(text)+ROW S(text)-SUM(1/COUNTIF(text
,text))),COUNTIF(text,"="&text)+ROWS(text)*NOT(IS TEXT(text)),))


text= your data areas
this formula return unique value and sort ascendenting..


Ciao Ivano!

=INDEX(text,MATCH(SMALL(IF(MATCH(text,text,)=ROW(t ext),COUNTIF(text,"="&tex
t)),ROW(text)),COUNTIF(text,"="&text),))
array formula = to be entered with CTRL+SHIFT+ENTER in a *range*

or, if you like best:
=INDEX(text,MATCH(SMALL(IF(MATCH(text,text,)=ROW(t ext),COUNTIF(text,"="&tex
t)),ROW(A1)),COUNTIF(text,"="&text),))
array formula = to be entered with CTRL+SHIFT+ENTER in a cell and then you
copy it down.

text is your range
This formula (as Ivano's one) works if 'text' starts from row 1.
And it's the same formula... just written in two different ways. (three
ways...)
[in you example, A1:A50, should work well]

Regards
Barbara

Good morning everyone!

i present a big thank you to all those who provided solutions to my
request.

The best thing about excel is you people....

Khaldoun


---
Message posted from http://www.ExcelForum.com/