Nice call Zac
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.
Nice call Zac
In A1:A13On 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.
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 ]
Jay,
Absolutely.
Chris
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 ]
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
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.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.
It's never too late to learn something new.
Ricky
Ricky,
There is no problem to fix. GG.
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 ]
Like this thread? Share it with others