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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
Thanks for reply,I have done what you written but nothing happens it gives me all zero values.Regards,
 
Upvote 0
Thanks it works.Whenever there is no match it gives me #N/A can I have zero instead of #N/A.Regards,
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
Aladin,

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

Paddy
 
Upvote 0
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].
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top