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