Hi Domenic,

Please ignore my previous Post. The Formula works Great!

My error: I entered the Formula and then copied it across the various cells
and then confirmed with CONTROL+SHIFT+ENTER , when all the cells were still
selected.

I should have entered the Formula in one selected cell and then confirmed
with CONTROL+SHIFT+ENTER, copying the Formula across the relevant cells
"AFTER" the CONTROL+SHIFT+ENTER was performed in the first cell.

Thank you for all your help - very much appreciated.

Cheers,
Sam

Domenic wrote:
I believe having the corresponding values returned to a single cell
would require either VBA or the use of the function MCONCAT which is
available in the free add-in Morefunc.xll.

Since I'm not familiar with VBA, and the add-in is not available for my
Mac version of Excel, I can only offer you a solution where the
corresponding values are returned to individual cells...

Assuming E1:F10 contains your data...

1) Let H1 contain your criterion, such as 1

2) Enter the following formula in I1 and copy across...

=IF(COLUMNS($I1:I1)<=COUNTIF($F$1:$F$10,$H1),INDE X($E$1:$E$10,SMALL(IF($F
$1:$F$10=$H1,ROW($E$1:$E$10)-ROW($E$1)+1),COLUMNS($I1:I1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi All,

[quoted text clipped - 29 lines]
Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1