Duplicates

[zacemmel]

I have seen several posts on the forum regarding duplicates. Most peope resort to UDFs and VBA to remove duplicates. That isn't neccesary. You can remove duplicates with the functions that are built-in to Excel.

Check out the formula I created below:

=IF(ROW()=ROW($A$1),$A$1,IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B1,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))))
This message was edited by zacemmel on 2002-05-02 11:02

 

[zacemmel]

I have been unable to break my formula using my rules. I can't quite seem to figure out what you are doing to break it. Maybe send me the XLS where the formula "breaks." I am curious.

BTW, John's formula isn't "pretty." You have to enter the array into an array that equals the number of unique records or else it returns #NUM!. I'm sure this could be cured by using iserror, but still.

 

[Etromania]

Nice call Zac

 

[Jay Petrulis]

On 2002-05-03 15:38, zacemmel wrote:
I have been unable to break my formula using my rules. I can't quite seem to figure out what you are doing to break it. Maybe send me the XLS where the formula "breaks." I am curious.

BTW, John's formula isn't "pretty." You have to enter the array into an array that equals the number of unique records or else it returns #NUM!. I'm sure this could be cured by using iserror, but still.

In A1:A13
Type {1,2,3,4,5,6,7,8,9,10,11,12,13}

In B1, B2:B13 enter your formulas.

Change A2 from 2 to 1 so that your column A sequence is
{1,1,3,4,5,6,7,8,9,10,11,12,13}

Only the first "1" will appear. B3:B13 will be blank. B2 will also be blank, but that is a duplicate.
This message was edited by Jay Petrulis on 2002-05-03 15:55

 

[Chris Davison]

Jay,

Absolutely.

Chris

 

[Chris Davison]

<TABLE BORDER="1" WIDTH="100%"BGCOLOR="white"><TR BGCOLOR="gray"><TD></TD><TD><CENTER>A</CENTER></TD><TD><CENTER>B</CENTER></TD><TD><CENTER>C</CENTER></TD><TD><CENTER>D</CENTER></TD><TD><CENTER>E</CENTER></TD><TD><CENTER>F</CENTER></TD><TD><CENTER>G</CENTER></TD><TD><CENTER>H</CENTER></TD><TD><CENTER>I</CENTER></TD><TD><CENTER>J</CENTER></TD></TR><TR><TD BGCOLOR="gray"><CENTER>1</CENTER></TD><TD ALIGN="right">jan</TD><TD ALIGN="right">=A1</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>2</CENTER></TD><TD ALIGN="right">111</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B1,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B1,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>3</CENTER></TD><TD ALIGN="right">mar</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B2,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B2,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>4</CENTER></TD><TD ALIGN="right">apr</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B3,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B3,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>5</CENTER></TD><TD ALIGN="right">may</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B4,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B4,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>6</CENTER></TD><TD ALIGN="right">jun</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B5,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B5,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>7</CENTER></TD><TD ALIGN="right">111</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B6,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B6,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>8</CENTER></TD><TD ALIGN="right">aug</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B7,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B7,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>9</CENTER></TD><TD ALIGN="right">sep</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B8,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B8,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>10</CENTER></TD><TD ALIGN="right">oct</TD><TD ALIGN="right">=ROW()=ROW($A$1)?$A$1 ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B9,$A$1:$A$10,0),MATCH($A$1:$A$10,$A$1:$A$10,0),99999))))?"" "a" MATCH($A$1:$A$10,$A$1:$A$10,0)>MATCH(B9,$A$1:$A$10,0)!MATCH($A$1:$A$10,$A$1:$A$10,0)</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>11</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>12</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>13</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>14</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>15</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>16</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>17</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>18</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>19</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>20</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR></TABLE>


gives the following results :<TABLE BORDER="1" WIDTH="100%"BGCOLOR="white"><TR BGCOLOR="gray"><TD></TD><TD><CENTER>A</CENTER></TD><TD><CENTER>B</CENTER></TD><TD><CENTER>C</CENTER></TD><TD><CENTER>D</CENTER></TD><TD><CENTER>E</CENTER></TD><TD><CENTER>F</CENTER></TD><TD><CENTER>G</CENTER></TD><TD><CENTER>H</CENTER></TD><TD><CENTER>I</CENTER></TD><TD><CENTER>J</CENTER></TD></TR><TR><TD BGCOLOR="gray"><CENTER>1</CENTER></TD><TD ALIGN="right">jan</TD><TD ALIGN="right">jan</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>2</CENTER></TD><TD ALIGN="right">111</TD><TD ALIGN="right">111</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>3</CENTER></TD><TD ALIGN="right">mar</TD><TD ALIGN="right">mar</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>4</CENTER></TD><TD ALIGN="right">apr</TD><TD ALIGN="right">apr</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>5</CENTER></TD><TD ALIGN="right">may</TD><TD ALIGN="right">may</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>6</CENTER></TD><TD ALIGN="right">jun</TD><TD ALIGN="right">jun</TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>7</CENTER></TD><TD ALIGN="right">111</TD><TD ALIGN="right"></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>8</CENTER></TD><TD ALIGN="right">aug</TD><TD ALIGN="right"></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>9</CENTER></TD><TD ALIGN="right">sep</TD><TD ALIGN="right"></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>10</CENTER></TD><TD ALIGN="right">oct</TD><TD ALIGN="right"></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>11</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>12</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>13</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>14</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>15</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>16</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>17</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>18</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>19</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR><TR><TD BGCOLOR="gray"><CENTER>20</CENTER></TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD><TD ALIGN="right"> </TD></TR></TABLE>





edit....... ignore this post : my formulae are corrupted !
This message was edited by Chris Davison on 2002-05-03 16:46

 

[barryt]

if in a1 i put "ThereIsAError"

=IF(ROW()=ROW($A$1),$A$1,IF(ISERROR(INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))),"thereisaerror",INDIRECT("a"&MIN(IF(MATCH($A$1:$A$13,$A$1:$A$13,0)>MATCH(B4,$A$1:$A$13,0),MATCH($A$1:$A$13,$A$1:$A$13,0),99999)))))

By doing this it doesn't make those blank spaces after the finding duplicate
There is still some issues to deal with but using this might help figure them out

 

[zacemmel]

I did exactly what you did and I got the correct result. I don't know what you are doing wrong, but obviously something.

 

[Ricky Morris]

On 2002-05-06 09:21, zacemmel wrote:
I did exactly what you did and I got the correct result. I don't know what you are doing wrong, but obviously something.

Zac, give it up. Your formula doesn't work as advertised. Look into the problem and fix it. See my post above with data that your formula didn't work with.

 

[zacemmel]

Ricky,

There is no problem to fix. GG.

 

[Chris Davison]

Zac,

please send me a copy of your file which works, I'd like to take a look at it

ta
Chris

novulari@hotmail.com
This message was edited by Chris Davison on 2002-05-06 10:04