Problem in Result of Formula

RAMU

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

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Active Member
Joined
Dec 11, 2009
Messages
298

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

RAMU

Active Member
Joined
Dec 11, 2009
Messages
298

ADVERTISEMENT

Dear All,

Can anyone pls help me ?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,516
Messages
5,596,617
Members
414,081
Latest member
Subaru_Steve

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
Top