I am trying to help an Analyst at a police department. She is trying to
search crime data in Excel and put it into a calendar. I found an Excel
Calendar on Microsoft's web site, each day is one cell. I then put a
VLOOKUP formula that goes to another sheet and returns the crime based
on the date. i.e.

8/1/2005 FALSE INFO TO OFFICER
8/1/2005 N&D/PARAPHERNALIA
8/1/2005 MUNI CODE/SOLICIT W/O PERMIT
8/2/2005 ASSAULT WITH DEADLY WEAPON
8/2/2005 BATTERY/SIMPLE

The problem I can't figure out how to Loop the formula multiple times
and CONCATENATE the results so I can paste them back into the Cell that
contains the date.

Any Ideas?

Lenny

Lenny Wrote:
I am trying to help an Analyst at a police department. She is trying to
search crime data in Excel and put it into a calendar. I found an
Excel
Calendar on Microsoft's web site, each day is one cell. I then put a
VLOOKUP formula that goes to another sheet and returns the crime based
on the date. i.e.

8/1/2005 FALSE INFO TO OFFICER
8/1/2005 N&D/PARAPHERNALIA
8/1/2005 MUNI CODE/SOLICIT W/O PERMIT
8/2/2005 ASSAULT WITH DEADLY WEAPON
8/2/2005 BATTERY/SIMPLE

The problem I can't figure out how to Loop the formula multiple times
and CONCATENATE the results so I can paste them back into the Cell
that
contains the date.

Any Ideas?

Lenny

One way ...

ASSUME that the dates are entered in Column A and the corresponding
crime descriptions are in Column B.

If the date is entered in, say, Cell C1, enter this formula in Cell C2


=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C$1,ROW($B $1:$B$100)),ROW(1:1)))

and copy down to suit your requirements.

BTW, the above is an array, so use "Ctrl+Shift+Enter" (instead of
simply doing "Enter") in committing this formula.

Hope this is what you need.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=471128

I don't think i explained it good enough let me try again.
In Sheet2 I have the following crime data and it could be a couple of
hundred rows

8/1/2005 FALSE INFO TO OFFICER
8/1/2005 N&D/PARAPHERNALIA
8/1/2005 MUNI CODE/SOLICIT W/O PERMIT
8/2/2005 ASSAULT WITH DEADLY WEAPON
8/2/2005 BATTERY/SIMPLE

I am in now back in Sheet1 Cell A1 I want a formula that will read
Sheet2 and Concatenate all the info for the first date like this into
one Cell
FALSE INFO TO OFFICER
N&D/PARAPHERNALIA
MUNI CODE/SOLICIT W/O PERMIT

I will write another formula that will put then data from the next date
into anohter cell untill I have all the data for the month.

I showed her how to do this with a pivot table but she wants it to look
like a calendar.

Thanks Again,
Lenny

Hi Lenny,

Try,

In Sheet1 B1,

=SUBSTITUTE(aconcat(IF(Sheet2!A1:A5=A1,Sheet2!B1:B 5),", "),",
False","")

where A1 houses Date

Confirm with Ctrl+Shift+Enter

ACONCAT is a Function and below is the code;


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


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=471128