Excel Forumula Help

[michael_allen_24]

I have a list of names such as:

1. Kay
2. Roy
3. Jay
4. Kay
5. Pete
6. Roy
7. Kay
8. Tom

These names might be repeating, such as Kay appearing 3 times above. How do I create a forumula to pull back the names in alphabetical order? But obviously omitting the repeating values? So if the forumula worked correctly I would have:

1. Jay
2. Kay
3. Pete
4. Roy
5. Tom

Thanks in advance

 

[JustinEP]

Thanks to everyone who has contributed to this thread - I have learned a lot!

I would like to use this variation of the formulas described so far:


=IFERROR(INDEX($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),MATCH(0,COUNTIF($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),"<"&$A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)))-SUM(COUNTIF($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),C$1:C1)),0)),"")

When using this formula, if there is a blank row before the variable in column A that would appear first if all the variables were sorted alphabetically, the formula results in a "0". Can this formula be modified to correct this issue?

Thanks again for all of the time put into analyzing this formula so far, this forum is an excellent resource!

 

[JustinEP]

Also, could the formula be adjusted to ignore blank cells that are blank as a result of a function?

 

[JustinEP]

I have found a work around for the issue, but it slows down the processing speed significantly. The original formula was looking for unique variables in column A and sorting them alphabetically in column C. I I adjusted the formula to look for unique variables in column B instead of A. In $B1 I added the formula =if($a1="","ZZZZ (Empty)",$a1). This adds the text "ZZZZ (Empty)" wherever the corresponding cell in column A is blank. The formula in column C adds "ZZZZ (Empty)" to the end of the list of responses, which I can work with for the purpose of what I am doing.

I would still like to find a way to modify the original formula so that I do not have to use the "helper" column (column B). The helper column really slows down the processing speed and it is not as clean.

Thanks in advance for all of your help!

 

[Aladin Akyurek]

Thanks to everyone who has contributed to this thread - I have learned a lot!

I would like to use this variation of the formulas described so far:


=IFERROR(INDEX($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),MATCH(0,COUNTIF($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),"<"&$A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)))-SUM(COUNTIF($A$2:INDEX($A:$A,MATCH(REPT("z",225),$A:$A)),C$1:C1)),0)),"")

When using this formula, if there is a blank row before the variable in column A that would appear first if all the variables were sorted alphabetically, the formula results in a "0". Can this formula be modified to correct this issue?

Thanks again for all of the time put into analyzing this formula so far, this forum is an excellent resource!

Also, could the formula be adjusted to ignore blank cells that are blank as a result of a function?

I have found a work around for the issue, but it slows down the processing speed significantly. The original formula was looking for unique variables in column A and sorting them alphabetically in column C. I I adjusted the formula to look for unique variables in column B instead of A. In $B1 I added the formula =if($a1="","ZZZZ (Empty)",$a1). This adds the text "ZZZZ (Empty)" wherever the corresponding cell in column A is blank. The formula in column C adds "ZZZZ (Empty)" to the end of the list of responses, which I can work with for the purpose of what I am doing.

I would still like to find a way to modify the original formula so that I do not have to use the "helper" column (column B). The helper column really slows down the processing speed and it is not as clean.

Thanks in advance for all of your help!

Let Sheet1, from A2 downwards, house the data of interest.

Define the following dynamic named ranges...

Odata (original data) as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",225),Sheet1!$A:$A))

Udata (unsorted original data) as referring to:

=INDEX(Odata,MATCH(TRUE,Odata<>"",0)):INDEX(Odata,MATCH(9.99999999999999E+307,1/(1-(Odata=""))))

C1: #Sorted#

C2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Udata,MATCH(0,COUNTIF(Udata,"<"&Udata)-SUM(COUNTIF(Udata,C$1:C1)),0)),"")

 

[maulbox]

need help formula with unsorted result...

[TABLE="width: 64"]
<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Jo
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[/TR]
[TR]
[TD]Sioux
[/TD]
[/TR]
[TR]
[TD]Chin
[/TD]
[/TR]
</tbody>[/TABLE]


tengkiu

 

[Aladin Akyurek]

need help formula with unsorted result...

[TABLE="width: 64"]
<TBODY>[TR]
[TD="width: 64"]Jo
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[/TR]
[TR]
[TD]Sioux
[/TD]
[/TR]
[TR]
[TD]Chin
[/TD]
[/TR]
</TBODY>[/TABLE]


tengkiu

Did you try post #74 above?

 

[maulbox]

Did you try post #74 above?

i did it, then the result is still sorted on C2

here the file ...

need result like on E2 list

tengkiu for helping

 

[Aladin Akyurek]

i did it, then the result is still sorted on C2

here the file ...

need result like on E2 list

tengkiu for helping

Unique list without sorting...

E2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$1122,SMALL(IF(FREQUENCY(IF($A$2:$A$1122<>"",
  MATCH($A$2:$A$1122,$A$2:$A$1122,0)),ROW($A$2:$A$1122)-ROW($A$2)+1),
  ROW($A$2:$A$1122)-ROW($A$2)+1),ROWS($E$2:E2))),"")

 

[maulbox]

Thank you ...

this formula working good

Unique list without sorting...

E2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$1122,SMALL(IF(FREQUENCY(IF($A$2:$A$1122<>"",
  MATCH($A$2:$A$1122,$A$2:$A$1122,0)),ROW($A$2:$A$1122)-ROW($A$2)+1),
  ROW($A$2:$A$1122)-ROW($A$2)+1),ROWS($E$2:E2))),"")

 

[Aladin Akyurek]

Thank you ...

this formula working good

You are welcome. Thanks for providing feedback.