Hi
if you like email me your file and I'll insert the
formulas for you.
email: frank[dot]kabel[at]mummert[dot]de
-----Original Message-----
It returns #N/A
Now, to clarify more, here is the data on sheet 1:
MDNNo rcdate rcamount
1723091070 4/29/04 2340
1723091070 5/11/04 2340
1723091070 6/11/04 5850
1723093698 6/14/04 2340
1723093756 4/28/04 2340
1723093756 6/16/04 3000
1723093756 6/23/04 2340
1723093756 6/28/04 2340
1723093756 7/3/04 3200
1723094031 4/19/04 2340
And data on Sheet 2 is:
A B C
MDN No. last rcdate last rcamount
1723091070 6/11/04 5850
1723093756 7/3/04 3200
where the value in column C is the value corresponding to
the date in column
B taken from sheet 1.
Please help to get the formula in column C.
thanx once again..
Hi
in this case it's a little bit simpler. enter the
following formula (also as array formula) in C12 on your
second sheet
=INDEX('sheet1'!$C$1:$C$200,MATCH(1,(recharge_Query!
A$1:A$2000=A2)*(recharge_Query!A$1:A$2000=B2),0))
-----Original Message-----
Yeah, thanx for that Frank.
But please explain it to use it like:
Sheet1 contain output from MS-Access as quoted in first
post....
A B C
Phone no. recharge date rc amount
Sheet2 contain this resultant:
A
B C
Phone No. Last Recharge Date
(=MAX(IF(recharge_Query! A$1:A$2000=A2,recharge_Query!
E$1:E$2000))
where (on sheet1 named as "recharge_Query")
A$1:A$2000=Phone No., E =
recharge date
Now, please suggest a formula so that i can get the
corresponding amount
equivalent to the value "Last recharge date"
I hope you understand the layman's language.
Please oblige and help.
o.k.
try the following array formula (entered with
CTRL+SHIFT+ENTER)
=INDEX(C1:C10,MATCH(1,(A1:A10=12345)*(B1:B10=MAX (IF
(A1:A10=12345,B1:B10))),0))
There 12345 is your telephone number. You can of
course
replace this with a cell reference. Lets assume you
use
cell E1 for this. Then change the formula to
=INDEX(C1:C10,MATCH(1,(A1:A10=E1)*(B1:B10=MAX(IF
(A1:A10=E1,B1:B10))),0))
-----Original Message-----
sorry frank
i din't got any formula..
can u resent it to this post please.
message
Hi
I think i posted a solution for you yesterday.
Have
you
tried it. If yes did you encounter an error and if
yes
what error?
-----Original Message-----
Hi
I've the following data in a file:
A
B C
Phone Recharge Date Amount
123456 4/14 2300
234567 6/10 5800
123456 5/14 5800
234567 7/10 3400
123456 6/14 3000
Earlier, i got a solution to find out the
MAXIMUM of
the "Active Date" e.g.
for Phone No. 234567 Last active date is 7/10
Now i want to get the LAST RECHARGED AMOUNT as
per
the
following format on a
new sheet:
eg.
Phone no. Last Recharge Dt. Last
Recharge
Amount
123456 6/14
3000
234567 7/10
3400
Please help.
Reg
Deepak
.
.
.
.
