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

 

[gijimbo]

Hi,

I'm looking for solution with combined text and numbers fields in Serial column.

Is there any simple modification of presented formulas, or I must use totally new approach.

Regards,

Bostjan

Hi,

You're question isn't clear.

Can I suggest you create a new Thread for your question and add in as much detail as possible. This will better ensure you get the answer you're after.

Matty

I think I've got the same problem as MoBo. The list contains both text and numbers as follows:

4561
A78942
56874-D
AA931
89113
RTSNG

It's odd to me that it works for just numbers and just text but doesn't when you've got both...

 

[Aladin Akyurek]

I think I've got the same problem as MoBo. The list contains both text and numbers as follows:

4561
A78942
56874-D
AA931
89113
RTSNG

It's odd to me that it works for just numbers and just text but doesn't when you've got both...

Care to state what the desired result is?

 

[gijimbo]

I'd like to get a sorted list of all unique values in the given range. Just sort numbers like text (they also happen to be stored as text). Like below:

Source:
AA
<blank>(blank)
B6
01
31
AA
21
<blank>(blank)
1A
2Z
01

Result:
01
1A
21
2Z
31
AA
B6</blank></blank>

 

[Aladin Akyurek]

I'd like to get a sorted list of all unique values in the given range. Just sort numbers like text (they also happen to be stored as text). Like below:

Source:
AA
<BLANK>(blank)
B6
01
31
AA
21
<BLANK>(blank)
1A
2Z
01

Result:
01
1A
21
2Z
31
AA
B6</BLANK></BLANK>

This is a difficult case for CountIf formulation. What follows makes use of intermediate steps in order to cope with blanks and text numbers...


[TABLE="width: 228"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]No blanks[/TD]
[TD="class: xl63, width: 90, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl63, width: 86, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[TD="class: xl63, bgcolor: transparent"]#intermediate[/TD]
[TD="class: xl63, bgcolor: transparent"]#Sorted[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]B6[/TD]
[TD="class: xl63, bgcolor: transparent"]@01[/TD]
[TD="class: xl63, bgcolor: transparent"]01[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]B6[/TD]
[TD="class: xl63, bgcolor: transparent"]@01[/TD]
[TD="class: xl63, bgcolor: transparent"]@1A[/TD]
[TD="class: xl63, bgcolor: transparent"]1A[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]01[/TD]
[TD="class: xl63, bgcolor: transparent"]@31[/TD]
[TD="class: xl63, bgcolor: transparent"]@21[/TD]
[TD="class: xl63, bgcolor: transparent"]21[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]31[/TD]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[TD="class: xl63, bgcolor: transparent"]@2Z[/TD]
[TD="class: xl63, bgcolor: transparent"]2Z[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[TD="class: xl63, bgcolor: transparent"]@21[/TD]
[TD="class: xl63, bgcolor: transparent"]@31[/TD]
[TD="class: xl63, bgcolor: transparent"]31[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]21[/TD]
[TD="class: xl63, bgcolor: transparent"]@1A[/TD]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[TD="class: xl63, bgcolor: transparent"]AA[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]@2Z[/TD]
[TD="class: xl63, bgcolor: transparent"]B6[/TD]
[TD="class: xl63, bgcolor: transparent"]B6[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]1A[/TD]
[TD="class: xl63, bgcolor: transparent"]@01[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]2Z[/TD]
[TD="class: xl63, bgcolor: transparent"]##[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]01[/TD]
[TD="class: xl63, bgcolor: transparent"]##[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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

=IFERROR(INDEX(IF(ISNUMBER(LEFT($A$2:$A$12)+0),"@"&$A$2:$A$12,$A$2:$A$12),
  SMALL(IF(1-($A$2:$A$12=""),ROW($A$2:$A$12)-ROW($A$2)+1),ROWS($B$2:B2))),"##")

C1, just enter:

=MATCH("##",$B$2:$B$12,0)-1

C3, control+shift+enter and copy down:

=IFERROR(INDEX($B$2:INDEX($B$2:$B$12,$C$1),
  MATCH(0,COUNTIF($B$2:INDEX($B$2:$B$12,$C$1),
  "<"&$B$2:INDEX($B$2:$B$12,$C$1))-
  SUM(COUNTIF($B$2:INDEX($B$2:$B$12,$C$1),"="&C$2:C2)),0)),"")

D3, just enter and copy down:

=IF($C3="","",SUBSTITUTE($C3,"@",""))

 

[gijimbo]

Well that mostly works with the exception of "2Z". It doesn't show on any of the result lists.

I tried adding more numbers to the bottom of the list and got it to show up but some of the entries I add don't show up.
It appears that the following is happening:
If you count the number of blanks in the original list, that is how many items are missed at the end of the list.
Ex:
If you have 5 blanks in the list, the last 5 items are missed.

 

[gijimbo]

I think I figured it out. I had the original list declared as a dynamic named range using the following formula:
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)
Unfortunately, because this uses the COUNTA function, the blank entries were shortening the named range.

One question though, couldn't you also just add the character "A" to the beginning of all the entries (kind of like you did with the @), have it sort them and then strip the "A" back out when returning the results?

 

[Aladin Akyurek]

I think I figured it out. I had the original list declared as a dynamic named range using the following formula:
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)
Unfortunately, because this uses the COUNTA function, the blank entries were shortening the named range.

One question though, couldn't you also just add the character "A" to the beginning of all the entries (kind of like you did with the @), have it sort them and then strip the "A" back out when returning the results?

Yes, indeed we could.

 

[CaptainDan8]

Re: Excel Forumula Help - Trick 759

This formula works perfectly if the data in the source ($A$2:A9 or myData) is text, however, it doesn't work with numbers even when formatted as text. Tests such as ISNUMBER() return FALSE and 1233 < 1234 return TRUE, however as soon as a second text string of all numbers is entered in the source list the Unique Sorted List becomes a single value of the first numbered (formatted as text) value (e.g. 1234).

Starting Source
Joe
Jo
Sioux
Chin
Dan
Chin

Result
Chin
Dan
Jo
Joe
Sioux

Change first Chin to 1234 (data source formatted as text)

Result
1234
Chin
Dan
Jo
Joe
Sioux

Change Dan to 1233

Source Source Now
Joe
Jo
Sioux
1234
1233
Chin

Result
1234


Help!!

 

[Pcubed]

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