Problem in Result of Formula

RAMU

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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
308
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
17,024
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
308

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
17,024
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
308

ADVERTISEMENT

Dear All,

Can anyone pls help me ?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,024
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
17,024
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,309
Messages
5,836,574
Members
430,438
Latest member
David Gr

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