Excel Forumula Help

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
 
Seems to work without any problem.

Please note that for this you will need to use Control+Shift+Enter to confirm the formula, instead of just Enter as you would for a regular formula. If done correctly, Excel will surround the formula with {}. Note that the {} cannot be entered manually.

<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 /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;font-style: italic;;">Serial #</td><td style="text-align: right;;"></td><td style="font-weight: bold;font-style: italic;;">Unique Sorted</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">427374</td><td style="text-align: right;;"></td><td style="text-align: right;;">403392</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">423549</td><td style="text-align: right;;"></td><td style="text-align: right;;">411826</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">412884</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">415317</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">419095</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">427817</td><td style="text-align: right;;"></td><td style="text-align: right;;">419978</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">421106</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">421852</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">422835</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">427374</td><td style="text-align: right;;"></td><td style="text-align: right;;">423549</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">403392</td><td style="text-align: right;;"></td><td style="text-align: right;;">423628</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">423628</td><td style="text-align: right;;"></td><td style="text-align: right;;">423731</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">431237</td><td style="text-align: right;;"></td><td style="text-align: right;;">424908</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">415317</td><td style="text-align: right;;"></td><td style="text-align: right;;">425120</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">430984</td><td style="text-align: right;;"></td><td style="text-align: right;;">426331</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">421852</td><td style="text-align: right;;"></td><td style="text-align: right;;">427374</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">415317</td><td style="text-align: right;;"></td><td style="text-align: right;;">427817</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">428581</td><td style="text-align: right;;"></td><td style="text-align: right;;">428581</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">423731</td><td style="text-align: right;;"></td><td style="text-align: right;;">430984</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">423731</td><td style="text-align: right;;"></td><td style="text-align: right;;">431237</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">426331</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">421852</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">419095</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">411826</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">419095</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">422835</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">422835</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">425120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">412884</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">421106</td><td style="text-align: right;;"></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">Sheet1</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">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$38,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$A$2:$A$38,"<"&$A$2:$A$38</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$38,$C$1:C1</font>)</font>),0</font>)</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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Thanks Sandeep, it does work when I enter it exactly but when I expand the range to include up to A500, which is the absolute maximum it will be, I get blanks. I'm using 2003 at work and trying it out on 2007 at home. Will that make any difference?

N
 
Upvote 0
The only difference would be that in 2003 you will not be able to use IFERROR.

If you still face problems, can you upload your workbook on a file upload site like box.net, and post the link here?

<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 /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Serial #</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Unique Sorted (XL 2007 & Higher)</td><td style="font-weight: bold;;">Unique Sorted (XL 2003)</td><td style="font-weight: bold;;">Unique Count</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">427374</td><td style="text-align: right;;"></td><td style="text-align: right;;">403392</td><td style="text-align: right;;">403392</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">423549</td><td style="text-align: right;;"></td><td style="text-align: right;;">411826</td><td style="text-align: right;;">411826</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">412884</td><td style="text-align: right;;">412884</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">415317</td><td style="text-align: right;;">415317</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td><td style="text-align: right;;">419095</td><td style="text-align: right;;">419095</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">427817</td><td style="text-align: right;;"></td><td style="text-align: right;;">419978</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">421106</td><td style="text-align: right;;">421106</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">421852</td><td style="text-align: right;;">421852</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">419978</td><td style="text-align: right;;"></td><td style="text-align: right;;">422835</td><td style="text-align: right;;">422835</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">427374</td><td style="text-align: right;;"></td><td style="text-align: right;;">423549</td><td style="text-align: right;;">423549</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">403392</td><td style="text-align: right;;"></td><td style="text-align: right;;">423628</td><td style="text-align: right;;">423628</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">423628</td><td style="text-align: right;;"></td><td style="text-align: right;;">423731</td><td style="text-align: right;;">423731</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">431237</td><td style="text-align: right;;"></td><td style="text-align: right;;">424908</td><td style="text-align: right;;">424908</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">415317</td><td style="text-align: right;;"></td><td style="text-align: right;;">425120</td><td style="text-align: right;;">425120</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">430984</td><td style="text-align: right;;"></td><td style="text-align: right;;">426331</td><td style="text-align: right;;">426331</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">421852</td><td style="text-align: right;;"></td><td style="text-align: right;;">427374</td><td style="text-align: right;;">427374</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">415317</td><td style="text-align: right;;"></td><td style="text-align: right;;">427817</td><td style="text-align: right;;">427817</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">428581</td><td style="text-align: right;;"></td><td style="text-align: right;;">428581</td><td style="text-align: right;;">428581</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">423731</td><td style="text-align: right;;"></td><td style="text-align: right;;">430984</td><td style="text-align: right;;">430984</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">423731</td><td style="text-align: right;;"></td><td style="text-align: right;;">431237</td><td style="text-align: right;;">431237</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">426331</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">421852</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">419095</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">411826</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">419095</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">422835</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">422835</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">425120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">412884</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">421106</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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">Sheet1</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">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$500,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$A$2:$A$500,"<"&$A$2:$A$500</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$500,$C$1:C1</font>)</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$D$1:D1</font>)<=$E$2,INDEX(<font color="Red">$A$2:$A$500,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$A$2:$A$500,"<"&$A$2:$A$500</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$500,$C$1:C1</font>)</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">FREQUENCY(<font color="Green">IF(<font color="Purple">$A$2:$A$500<>"",MATCH(<font color="Teal">"~"&$A$2:$A$500&"",$A$2:$A$500&"",0</font>)</font>),ROW(<font color="Purple">$A$2:$A$500</font>)-ROW(<font color="Purple">$A$2</font>)+1</font>),1</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$500,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$A$2:$A$500,"<"&$A$2:$A$500</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$500,$C$1:C2</font>)</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$D$1:D2</font>)<=$E$2,INDEX(<font color="Red">$A$2:$A$500,MATCH(<font color="Green">0,COUNTIF(<font color="Purple">$A$2:$A$500,"<"&$A$2:$A$500</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$500,$C$1:C2</font>)</font>),0</font>)</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
Thanks again Sandeep, I'll keep trying that. I suspect the problem might partly user error because it's worked once and won't do it again! I'm not back in the office until Wednesday now but I'll keep you posted.
 
Upvote 0
In a further update, the original formula you posted running to A38 has now worked and picked up all 69 unique serial numbers in the original dataset (on 2007)! Thank you Sandeep, the problem was definitely at this end!
 
Upvote 0
Hi Sandeep, I've tried the formula on 2003 at work and I'm still getting blanks. I've put the original on box.net as requested. Could you have a look at please? I do still think the problem is likely to be me though.
 
Upvote 0
Hi

Actually, the formula does error out at times when there are blank cells (sorry for saying otherwise earlier), and it does error out if the blanks are formula blanks (i.e. the result of a formula), which is the case in your file.

This modification works in case of blank cells, but only if all the cells are regular blanks, or all the cells are formula blanks. This won't work if your range contains a mixture of regular blanks and formula blanks.

Your best bet for a formula that works in all conditions is the one posted in Post# 25 by Mike.

<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 /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Serial #</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Unique</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">427374</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">269287</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">423549</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">403392</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">424908</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">403604</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">424908</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">411516</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">424908</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">411577</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">427817</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">411826</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">419978</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">412884</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">419978</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">413824</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">419978</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">415317</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">427374</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">415892</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">403392</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">417066</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">423628</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">418346</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">431237</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">419095</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">415317</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">419892</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">430984</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">419978</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">421852</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">420806</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">415317</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">421085</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">428581</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">421106</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">423731</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">421564</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">423731</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">421852</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">426331</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">422659</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">421852</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;">422835</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">419095</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">423549</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">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$551,MATCH(<font color="Green">0,IF(<font color="Purple">LEN(<font color="Teal">$A$2:$A$551</font>)>0,COUNTIF(<font color="Teal">$A$2:$A$551,"<"&$A$2:$A$551</font>),-1</font>)-SUM(<font color="Purple">COUNTIF(<font color="Teal">$A$2:$A$551,$C$1:C1</font>)</font>),0</font>)</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
Thanks again Sandeep, I thought it was me going mad. The problem with the formula posted is that you said that 'IFERROR' won't work on 2003 which is what I'm stuck with at work.
 
Upvote 0

Forum statistics

Threads
1,224,527
Messages
6,179,328
Members
452,907
Latest member
Roland Deschain

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