match dates

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
Book1
ABCDE
1DAYSINCEA/COPENPAYMENDATEAMOUNT
21-Jan-023-Jan-02200
32-Jan-025-Jan-02500
43-Jan-026-Jan-02400
54-Jan-028-Jan-02300
65-Jan-029-Jan-02200
76-Jan-0211-Jan-02800
87-Jan-0213-Jan-02200
98-Jan-02
109-Jan-02
1110-Jan-02
1211-Jan-02
1312-Jan-02
1413-Jan-02
1514-Jan-02
Sheet1


Refer to above excel sheet i need to move the payment dates with amount to the column B infront of exact date of column a means 3 jan with amount of 200 has to come to b4.Please if anyone could help writing formula or macro.

Thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Enter the formula...

=VLOOKUP(A2,D$2:E$8,2,0)

...into B2 and fill down.
This message was edited by Mark W. on 2002-09-20 12:29
 

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
Thanks for reply,I have done what you written but nothing happens it gives me all zero values.Regards,
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
I'm sorry I made a typo :( Use this formula...

=VLOOKUP(A2,D$2:E$8,2,0)
 

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200

ADVERTISEMENT

Thanks it works.Whenever there is no match it gives me #N/A can I have zero instead of #N/A.Regards,
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
On 2002-09-20 20:54, ehsas wrote:
Thanks it works.Whenever there is no match it gives me #N/A can I have zero instead of #N/A.Regards,

The formula I show in the worksheet simulation has some redundnacy, but it does what you want ...
Book3
ABCDE
1DaysSincAcctOpenPmtDateAmount
21-Jan-02 3-Jan-02200
32-Jan-02 5-Jan-02500
43-Jan-022006-Jan-02400
54-Jan-02 8-Jan-02300
65-Jan-025009-Jan-02200
76-Jan-0240011-Jan-02800
87-Jan-02 13-Jan-02200
98-Jan-02300
109-Jan-02200
1110-Jan-02 
1211-Jan-02800
1312-Jan-02 
1413-Jan-02200
1514-Jan-02 
Sheet7
</SPAN>

Regards!

Yogi
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-09-20 20:54, ehsas wrote:
Thanks it works.Whenever there is no match it gives me #N/A can I have zero instead of #N/A.Regards,

My standard answer is to use one of:

[1]

=IF(ISNA(SETV(VLOOKUP(A2,D$2:E$8,2,0))),0,GETV())

if you install the add-in morefunc.xll, downloadable from:

http://longre.free.fr/english/index.html

[2]

=IF(ISNUMBER(MATCH(A2,D$2:D$8,0)),VLOOKUP(A2,D$2:E$8,2,0),0)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Aladin,

Other than reducing the ware on your fingers, what's the advantage to using setv() etc over match() here?

Paddy
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-21 00:55, PaddyD wrote:
Aladin,

Other than reducing the ware on your fingers, what's the advantage to using setv() etc over match() here?

Paddy

Given the fact that Longre's functions (programmed in a variant of C, not in VBA) are as fast as built-in functions, I expect [1] to be faster than [2].
 

Forum statistics

Threads
1,144,770
Messages
5,726,186
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top