HELP.. formula required

D

Deepak

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
 
F

Frank Kabel

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?
 
F

Frank Kabel

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))
 
D

Deepak

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.
 
F

Frank Kabel

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
 
D

Deepak

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..
 
F

Frank Kabel

Hi
if you like email me your file and I'll insert the
formulas for you.
email: frank[dot]kabel[at]mummert[dot]de
 
D

Deepak

sir
the mail has been sent to you


Frank Kabel said:
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..




.
 
F

Frank Kabel

File is on its way back. The formula in cell E2 is the
array formula:
=INDEX(Sheet1!$E$1:$E$50,MATCH(1,(Sheet1!$D$1:$D$50=Sheet2!
D2)*(Sheet1!$A$1:$A$50=Sheet2!A2),0))

-----Original Message-----
sir
the mail has been sent to you


Frank Kabel said:
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.


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




.



.



.



.


.
 
D

Deepak

FRANK

IT'S REALLY GREAT TO SEE THE RESULTS.....

THANX TO YOU.

Frank Kabel said:
File is on its way back. The formula in cell E2 is the
array formula:
=INDEX(Sheet1!$E$1:$E$50,MATCH(1,(Sheet1!$D$1:$D$50=Sheet2!
D2)*(Sheet1!$A$1:$A$50=Sheet2!A2),0))

-----Original Message-----
sir
the mail has been sent to you


Frank Kabel said:
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




.



.



.



.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top