Hello,Sir Bernd,

calculation time is worth of it and this multicat function is good, yet is
there any way to have a final result something like this

{3,6,7,8,10}

i've made a few run on it and have a result of
3,6,7,8,10,

i need it as part of a lookup..if you can please

best regards,
driller

" wrote:

Hello,

If your values are in A1:A99 then enter as array formula:
=multicat(IF(ISNA(MATCH(ROW(INDIRECT("1:"&MAX(A1:A 99))),A1:A99,)),ROW(INDIRECT("1:"&MAX(A1:A99)))&", ",""))

The UDF multicat you have to copy into a module (press ALT + F11,
insert a module and then insert code shown below):
'********************************************
'Purpose: Concatenate all cells in a range or
' array
'Inputs: vP - range/array to be concatenated
' sDelim - optional delimiter to be
' inserted between text parts
'Returns: Concatenated string
'*****************************************
Function MultiCat( _
ByRef vP As Variant, _
Optional ByVal sDelim As String = "") _
As String
Dim vE As Variant
For Each vE In vP
MultiCat = MultiCat & sDelim & vE
Next vE
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function

Regards,
Bernd