Problem in Result of Formula

[RAMU]

Dear All,

I have a worksheet like mentioned below:

A B C
code date Rate
CM20000001 22-Sep-09 12000
CM20000002 9-Sep-10 135
CM20000002 3-Jul-10 135
CM20000002 22-Jan-10 98
CM20000002 5-Nov-09 98
CM20000002 12-Aug-09 175
CM20000009 6-Nov-09 151
CM20000018 26-Aug-10 65
CM20000020 19-Aug-10 2319.94
CM20000020 19-Nov-09 99
CM20000020 19-Nov-09 481.73
CM20000023 19-Nov-10 1250
CM20000055 31-Jan-11 466.5
CM20000055 25-Nov-10 466.5
CM20000055 27-Oct-10 466.5
CM20000055 27-Oct-10 466.5
CM20000055 7-Aug-10 466.5
CM20000055 26-Apr-10 455.1

In column F am using a formula =IF(ISBLANK(E2),"",IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-1"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-2"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-3"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-4"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-5"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-5"),D:D,FALSE),1))&"/"&IF(ISERROR(INDEX(B:B,MATCH(CONCATENATE(E2,"-6"),D:D,FALSE),1)),"",INDEX(B:B,MATCH(CONCATENATE(E2,"-6"),D:D,FALSE),1)))

In Column E the code CM20000055 is mentioned.

For that in Column F result is showing 40574/40507/40478/40478/40397/40294

But I want the dates. In spite of formatting the cell in Date, it is not showing my desired result.

Pls help me.

 

[Aladin Akyurek]

Try to track down some examples (on this board) with ACONCAT where multiple results are collected into a single cell. That said, see the following for how to:

=TEXT(A2,"dd-mmm-yy")&" is an example"

where A2 houses a date.

 

[RAMU]

Try to track down some examples (on this board) with ACONCAT where multiple results are collected into a single cell. That said, see the following for how to:

=TEXT(A2,"dd-mmm-yy")&" is an example"

where A2 houses a date.

Dear Aladin,

Sorry, Can't get you friend. Where should I use this formula ?

 

[Marcelo Branco]

Hi,

Assuming the data in A1:C19
E1 = CM20000055
and if its ok the results in F1, F2....

maybe this

Formula in F1
=IF(ROWS($1:1)>COUNTIF($A$2:$A$19,$E$1),"", INDEX(B$2:B$19,SMALL(IF($A$2:$A$19=$E$1,ROW($A$2:$A$19)-ROW($A$2)+1),ROWS($1:1))))
Confirmed with Ctrl+Shift+Enter
copy down

HTH

M.

 

[RAMU]

Dear Branco,

Your formula can give me only last purchase date, though the result would not come in same format. it will show 40574.

See I want that the result will come out in date format after applying my formula.

 

[Marcelo Branco]

Dear Branco,

Your formula can give me only last purchase date, though the result would not come in same format. it will show 40574.

See I want that the result will come out in date format after applying my formula.

Dear Ramu,

Did you copy down the formula to F2, F3...? If so, all you have to do is format the cells as date.

If you do need the all the results in the same cell you have to use the ACONCAT function as Aladin has said (he teached me how and when to use this function. Tks one more time AA )

See below

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

HTH

M.

 

[RAMU]

Dear All,

Can anyone pls help me ?

 

[Marcelo Branco]

Ramu,

If you need the results on a single cell and formated as dates put my formula in a helper-column, say in X1, and copy down to X2, X3...
Format X1, X2... as Date

Copy the function Aconcat (Ctrl+C) and paste it (Ctrl+V) on a standard module (Alt+F11, Insert Module and paste the function on the right panel)

Then in F1
=TRIM(ACONCAT(X1:X20," "))

save the file as macro-enabled

HTH

M.

 

[Aladin Akyurek]

Dear Aladin,

Sorry, Can't get you friend. Where should I use this formula ?

That example shows you how you can coerce a formula result (an integer) into a date format...

 

[Marcelo Branco]

Sorry about my previous post. I've complicated the things unnecessarily. I'm tired...

I think this should work

Formula in F1
=TRIM(ACONCAT(IF(A2:A19=E1,TEXT(B2:B19,"dd-mmm-yyyy"),"")," "))
Ctrl+Shift+Enter

M.