In article ,
Toby wrote:
Thanks again for the quick reply. It wouldn't be a problem to return
locations in different cells, so a formula solution for that would be great.
Based on the same assumptions as previously, try...
F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
=SUM(IF(FREQUENCY(IF($A$2:$A$100=$E2,IF($C$2:$C$10 0<"",MATCH("~"&$C$2:$C
$100,$C$2:$C$100&"",0))),ROW($C$2:$C$10)-ROW($C$2)+1),1))
G2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:
=IF(COLUMNS($G2:G2)<=$F2,INDEX($C$2:$C$100,SMALL(I F(FREQUENCY(IF($A$2:$A$
100=$E2,IF($C$2:$C$100<"",MATCH("~"&$C$2:$C$100,$ C$2:$C$100&"",0))),ROW(
$C$2:$C$100)-ROW($C$2)+1),ROW($C$2:$C$100)-ROW($C$2)+1),COLUMNS($G2:G2)))
,"")
I'll also talk to my IT folks to see if they'll allow me to install the excel
addon you referenced. Looks like a lot of great stuff there.
....and more efficient, too.
--
Domenic
http://www.xl-central.com