# Problem in Result of Formula

#### RAMU

##### Active Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

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.

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

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.

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 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

Code:
``````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.

Dear All,

Can anyone pls help me ?

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.

Last edited:

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...

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.

Replies
3
Views
627
Replies
5
Views
233
Replies
19
Views
776
Replies
6
Views
578
Replies
8
Views
399

1,217,760
Messages
6,138,451
Members
450,137
Latest member
HANHAN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back