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