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

 

[Sandeep Warrier]

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 />

 

[tagman]

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

 

[tagman]

I've just noticed that it seems to be a problem with blank lines, of which there are many....

 

[Sandeep Warrier]

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 />

 

[tagman]

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.

 

[tagman]

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!

 

[tagman]

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.

 

[Sandeep Warrier]

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 />

 

[tagman]

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.

 

[Sandeep Warrier]

IFERROR won't work in 2003. It was introduced in 2007.

An alternate to IFERROR was posted in Post#54.