Hi
I've found an array I want to manipulate to generate between 10 and 20 unique ID references (alpha numeric) based on data I put into column A. I don't want to tell the formula how many rows of data I want it to look at. I need it to check that itself. I don't want to use VBA.
=INDEX($A$2:$A$19,LARGE(MATCH(ROW($A$2:$A$19),ROW($A$2:$A$19))*NOT(COUNTIF($B$1:B1,$A$2:$A$19)),RANDBETWEEN(1,ROWS($A$2:$A$19)-ROW(A1)+1))) (Ctrl+shift+enter)
There are usually more (or less) than 19 rows of data so rather than it stating A2:A19, I want it to do A2:A[whatever the last row is]. I've tried to use CountA in the array but that doesn't work.
I've attached the sample I'm currently working on so you can see what I mean.
View attachment Unique numbers.xls
Thanks.
I've found an array I want to manipulate to generate between 10 and 20 unique ID references (alpha numeric) based on data I put into column A. I don't want to tell the formula how many rows of data I want it to look at. I need it to check that itself. I don't want to use VBA.
=INDEX($A$2:$A$19,LARGE(MATCH(ROW($A$2:$A$19),ROW($A$2:$A$19))*NOT(COUNTIF($B$1:B1,$A$2:$A$19)),RANDBETWEEN(1,ROWS($A$2:$A$19)-ROW(A1)+1))) (Ctrl+shift+enter)
There are usually more (or less) than 19 rows of data so rather than it stating A2:A19, I want it to do A2:A[whatever the last row is]. I've tried to use CountA in the array but that doesn't work.
I've attached the sample I'm currently working on so you can see what I mean.
View attachment Unique numbers.xls
Thanks.