richardjshaffer
Board Regular
- Joined
- Oct 9, 2008
- Messages
- 84
Hi,
hope someone can help, I'm really frustrated.
I found this great array formula seaching online that gathers all the unique items in a range A1:A10.
I wish I understood what's happening, can anyone explain this in layman terms?
{=INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW(INDIRECT("1:"&ROWS($A$1:$A$10))),MATCH($A$1:$A$10,$A$1:$A$10,0),""),ROW(INDIRECT("1:"&ROWS($A$1:$A$10)))))}
Even just to understand the last bit ROW(INDIRECT("1:"&ROWS($A$1:$A$10) would be great - isn't the row of the indirectly chosen range impossible, it's range not a cell?
many thanks,
Richard
hope someone can help, I'm really frustrated.
I found this great array formula seaching online that gathers all the unique items in a range A1:A10.
I wish I understood what's happening, can anyone explain this in layman terms?
{=INDEX($A$1:$A$10,SMALL(IF(MATCH($A$1:$A$10,$A$1:$A$10,0)=ROW(INDIRECT("1:"&ROWS($A$1:$A$10))),MATCH($A$1:$A$10,$A$1:$A$10,0),""),ROW(INDIRECT("1:"&ROWS($A$1:$A$10)))))}
Even just to understand the last bit ROW(INDIRECT("1:"&ROWS($A$1:$A$10) would be great - isn't the row of the indirectly chosen range impossible, it's range not a cell?
many thanks,
Richard