Thanks:  0
Likes:  0

1. 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.

2. Nice call Zac

3. 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 ]

4. Jay,

Absolutely.

Chris

5.  A B C D E F G H I J 1 jan =A1 2 111 =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) 3 mar =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) 4 apr =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) 5 may =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) 6 jun =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) 7 111 =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) 8 aug =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) 9 sep =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) 10 oct =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) 11 12 13 14 15 16 17 18 19 20

gives the following results :

 A B C D E F G H I J 1 jan jan 2 111 111 3 mar mar 4 apr apr 5 may may 6 jun jun 7 111 8 aug 9 sep 10 oct 11 12 13 14 15 16 17 18 19 20

edit....... ignore this post : my formulae are corrupted !

[ This Message was edited by: Chris Davison on 2002-05-03 16:46 ]

6. 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

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

8. 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.

9. Ricky,

There is no problem to fix. GG.

10. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•