|Posted: January 19, 2004, 10:54 pm - IP Logged|
off the top of my head i would do this for each row of 5 digits, [replace the lookup_array with your 5 coloum row then copy this formula on down the rows of numbers] you can replace the 11, 22 & 32 by making them look at cell value that you can change to any numbers if you want
=if(and(iserror(MATCH( 11, lookup_array, 0)),iserror(MATCH(22, lookup_array, 0)),iserror(MATCH(32, lookup_array, 0))),1,0)
now when ever none of all three of those numbers are in that row of 5 numbers a 1 will be shown, otherwise if any of those number exists in that row a 0 will show up.
now all you do in the next 5 columns next to your 1 or 0 is say ok, if it's a 1 then put the 1st of the 5 numbers in this column, etc till you do all 5 etc etc