Excel Forumula Help

M

michael_allen_24

Board Regular
Joined
Dec 29, 2010
Messages
64
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Hi and welcome to the Board!

The easiest way to do this is to use Advanced Filter and Sort.

Suppose your data is in A2:A9. Ensure that A1 has a heading which has been formatted different to the data (eg. with bold/italics etc)

Select the range A1:A9 and go to Data | Filter | Advanced Filter.

Select the option that says Copy to another location. In the Copy to range box, type in (or select) the cell you want to extract the info to (C1 in this example).

Check the box that says Unique records only and click on OK.

Now you will have unique records from your dataset in column C.

Select the data in column C and sort it via Data | Sort.

Hope this helps.
 
Upvote 0
Please make the layout as seen below
enter the formula in the following cells and drag till row 9 as seen below.

Cell B2: =IFERROR(INDEX($A$2:$A$9,MATCH(SMALL($C$2:$C$9,ROWS($B$2:B2)),$C$2:$C$9,0)),"")

Cell C2
=IF(COUNTIF($A$2:A2,"="&A2)>1,"",CODE(A2))



<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABC
1NameResultHelper Cell
2KayJay75
3RoyKay82
4JayPete74
5KayRoy 
6PeteTom80
7Roy  
8Kay  
9Tom 84
Sheet1


</body></html>
 
Upvote 0
Using formula, sorting by up to the left-most 20 characters in the name, the following demonstrates how to achieve with the use of two helper columns:

Excel Workbook
ABC
1Unsorted
2NameSort KeyRank
3Richard Schollar5.04135E+394
4Jonmo4.24746E+393
5VOG5.44739E+397
6Colin Legg3.54744E+391
7RoryA5.04751E+396
8VOG5.44739E+398
9Jon von der Heyden4.24746E+392
10Richard Schollar5.04135E+395
Sheet3
Excel 2010
Cell Formulas
RangeFormula
B3=SUMPRODUCT(CODE(MID(UPPER(A3&REPT("a",20-LEN(A3))),ROW($1:$20),1))-32,100^(21-ROW($1:$20)))/100
C3=RANK(B3,$B$3:$B$10,1)+COUNTIF($B$3:B3,B3)-1



Excel Workbook
A
12Sorted
13Name
14Colin Legg
15Jon von der Heyden
16Jonmo
17Richard Schollar
18RoryA
Sheet3
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
 
Upvote 0
My two cents :)

Formula in B2 is copied down as far as required:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Unique Sorted List</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Kay</td><td style=";">Jay</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Roy</td><td style=";">Kay</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jay</td><td style=";">Pete</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Kay</td><td style=";">Roy</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Pete</td><td style=";">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Roy</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Kay</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Tom</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">{=INDEX(<font color="Blue">$A$2:$A$9,MATCH(<font color="Red">0,COUNTIF(<font color="Green">$A$2:$A$9,"<"&$A$2:$A$9</font>)-SUM(<font color="Green">COUNTIF(<font color="Purple">$A$2:$A$9,"="&B$1:B1</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Upvote 0
You {moderator deleted}! I only just found the thread with that formula myself! :(
 
Last edited by a moderator:
Upvote 0
Thanks for all you help guys! I think I found what I am looking for: One other quick question. This is the formula I used to pull back the information. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Array Formulas <o:p></o:p>
<TABLE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; WIDTH: 100%; BORDER-COLLAPSE: collapse; BACKGROUND: white; BORDER-TOP: medium none; BORDER-RIGHT: medium none; mso-border-alt: solid #BBBBBB .75pt; mso-yfti-tbllook: 1184; mso-padding-alt: 1.5pt 1.5pt 1.5pt 1.5pt" class=MsoNormalTable border=1 cellSpacing=0 cellPadding=0 width="100%"><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #bbbbbb 1pt solid; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; WIDTH: 7.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #dae7f5; BORDER-TOP: #bbbbbb 1pt solid; BORDER-RIGHT: #ece9d8; PADDING-TOP: 1.5pt; mso-border-top-alt: solid #BBBBBB .75pt; mso-border-left-alt: solid #BBBBBB .75pt" width=10>
Cell<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 3.75pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #dae7f5; BORDER-TOP: #bbbbbb 1pt solid; BORDER-RIGHT: #bbbbbb 1pt solid; PADDING-TOP: 1.5pt; mso-border-top-alt: solid #BBBBBB .75pt; mso-border-right-alt: solid #BBBBBB .75pt">Formula<o:p></o:p>
</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #bbbbbb 1pt solid; BORDER-LEFT: #bbbbbb 1pt solid; PADDING-BOTTOM: 1.5pt; PADDING-LEFT: 1.5pt; WIDTH: 7.5pt; PADDING-RIGHT: 1.5pt; BACKGROUND: #dae7f5; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 1.5pt; mso-border-left-alt: solid #BBBBBB .75pt; mso-border-bottom-alt: solid #BBBBBB .75pt" width=10>
B2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #bbbbbb 1pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 1.5pt; BACKGROUND-COLOR: transparent; PADDING-LEFT: 1.5pt; PADDING-RIGHT: 1.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #bbbbbb 1pt solid; PADDING-TOP: 1.5pt; mso-border-right-alt: solid #BBBBBB .75pt; mso-border-bottom-alt: solid #BBBBBB .75pt">{=INDEX($A$2:$A$9,MATCH(0,COUNTIF($A$2:$A$9,"<"&$A$2:$A$9)-SUM(COUNTIF($A$2:$A$9,"="&B$1:B1)),0))}<o:p></o:p>
</TD></TR></TBODY></TABLE>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself<o:p></o:p>
<o:p></o:p>
I will not always know the boundaries of the report being posted into the sheet. So for example, sometimes my names might end at A9, sometimes it might end at A100. How do I accommodate for this? It seems like when I switch the formula around to capture a bigger array everything is fine until I change this part of the formula (SUM(COUNTIF($A$2:$A$9). Is there a way to fix this? <o:p></o:p>
<o:p></o:p>
Thanks again, and I apologize if these are simple questions but you are really helping me out. Thanks again.<o:p></o:p>
 
Upvote 0
Michael

Just apply a named range to your data and make the formula use that named range. You can even make the name dynamic so that it will expand and contract as data is added/taken away from your list:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">Unique Sorted List</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Kay</td><td style=";">Jay</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Roy</td><td style=";">Kay</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jay</td><td style=";">Pete</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Kay</td><td style=";">Roy</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Pete</td><td style=";">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Roy</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Kay</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Tom</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=INDEX(<font color="Blue">myData,MATCH(<font color="Red">0,COUNTIF(<font color="Green">myData,"<"&myData</font>)-SUM(<font color="Green">COUNTIF(<font color="Purple">myData,"="&B$1:B1</font>)</font>),0</font>)</font>)</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">myData</th><td style="text-align:left">=Sheet3!$A$2:INDEX(<font color="Blue">Sheet3!$A:$A,MATCH(<font color="Red">"zzzz",Sheet3!$A:$A,1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Count Number of Times Values appear in a Column and Rank top 3
Replies
5
Views
171
Fluff
Fluff
W
  • Question
Macro/VBA code code for creating new excel file for each pivot table from a column filter
Replies
1
Views
297
Anthony47
A
G
  • Solved
Removing duplicates rows
Replies
7
Views
353
Gcsingh90
G
M
  • Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
111
dreid1011
dreid1011
T
  • Solved
Adding additional text to a value pulled from another sheet
Replies
8
Views
436
Snakehips
Snakehips

Forum statistics

Threads
1,221,900
Messages
6,162,691
Members
451,782
Latest member
LizN

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top