Problem in Result of Formula

RAMU

Active Member
Joined
Dec 11, 2009
Messages
321
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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