|
|
|
|
Worked like a charm. Thanks.
"Ragdyer" wrote in message
...
With your example starting in A1, with your title row,
And your data extending from A2 to F11,
And your *oldest* date at the bottom:
And your number list starting in H2,
And your Countif formula starting in I2,
Enter this *array* formula in J2:
=INDEX($A$1:$A$11,MIN(IF($A$2:$F$11=H2,ROW($A$2:$A $11))))
and double click the fill handle to copy this down Column J as far as
there
is data in Column I.
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
If a number in Column H is *not* present in your data list, the formula
will
return A1 (DATE).
Also, make sure that Column J is formatted as a date.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"gonowhere" wrote
in message ...
Hi, new to the forum but I've been reading it for a couple of days now.
Looking forward to talking and learning with everyone. I think I am
better than average with Excel but this problem is driving me nuts! I
have a list of lottery numbers listed by date drawn (see below for
example) on one worksheet. On another sheet I have a count of how many
times a number has been drawn. I would like to add a column to show the
last date a number was drawn and have it update automatically when I add
a new drawing. However, I haven't been able to figure out how to get
the date to update automatically.
_Example:_
Date 1st 2nd 3rd 4th 5th
28-May-05 05 07 24 28 39
25-May-05 27 29 31 42 46
21-May-05 02 05 07 10 30
18-May-05 07 13 17 20 35
14-May-05 21 29 38 49 52
11-May-05 05 25 46 48 49
07-May-05 21 23 39 44 47
04-May-05 10 21 27 33 40
30-Apr-05 08 15 17 32 48
27-Apr-05 11 13 18 32 42
Number Times Picked
01 4
02 2
03 2
04 2
05 6
06 4
07 8
08 3
09 8
10 8
The dates and each number appears in their own cells across six columns
(A thru F). I used COUNTIF to get the total number of times picked.
Long story short, I would like to add a third column to show the last
date that a number was picked. So, for example, if the numbers "3, 5,
7, 8 and 10" were drawn on "1 June 05" the "date drawn" column would
show the 1 June date for those 5 numbers. I hope someone can help me.
Btw, I know from reading some other posts that the lottery is not a
favorite subject to discuss with some people but rest assured that I
only do it for fun. I know there is nothing I can do in Excel that
will help me win a random draw lottery.
Thanks in advance for any help,
Mike
--
gonowhere
------------------------------------------------------------------------
gonowhere's Profile:
http://www.excelforum.com/member.php...o&userid=23849
View this thread:
http://www.excelforum.com/showthread...hreadid=374986
|