VBA code to match id and Date from sheet1 with Sheet 2 and copy matching rows and unmatched with blank

motherindia

Board Regular
Joined
Oct 15, 2015
Messages
218
Hi Friends,

I Have two Sheets.

Sheet 1 Contains following data;




ID No
Country
Sdate
E Date
Type
Nature
Text Contetns
Days
1111111
Finland
26-Jun-15
1-Jul-15
LD
Paid
ALL
6
1111111
Ireland
2-Jul-15
7-Jul-15
LD
Paid
ALL
6
1111111
Ireland
8-Jul-15
8-Jul-15
LD
Paid
ALL
0.5
1111111
Ireland
8-Jul-15
8-Jul-15
PD
unpaid
ALL
0.5

<tbody>
</tbody>



Sheet2 Contains
I want to math ID NO and Date of Shee2 with Sheet1 and copy the matching rows to Sheet3. If no matches found then it should be blank










<tbody>
</tbody>


ID NO Date
1111111
26-Jun-15
1111111
27-Jun-15
1111111
28-Jun-15
1111111
29-Jun-15
1111111
30-Jun-15
1111111
01-Jul-15
1111111
02-Jul-15
1111111
03-Jul-15
1111111
04-Jul-15
1111111
05-Jul-15
1111111
06-Jul-15
1111111
07-Jul-15
1111111
08-Jul-15
1111111
10-Jul-15
1111111
11-Jul-15
1111111
12-Jul-15

<tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Which date in Sheet 1 are you matching? Also, to be sure we understand, based upon your example, what should sheet 3 look like?
 
Upvote 0
Thanks for quick response sir.

Basically I need to check if ID match in both sheet, then I need to match Date in Sheet2 between start date and end date in Sheet1 . If Match found then it should be copied to Sheet 3. In case there is duplicate date in SHeet1 then it should be copied as it is. If Date in SHeet 1 is not in Sheet 2 against matching ID, then it has to copied to 3rd Sheet with ID and date of Sheet2.

After running macro the result should be as follows;


Emp noDaysCountryTypeNatureText ContetnsDays
111111126-Jun-15FinlandLDPaidALL1
111111127-Jun-15FinlandLDPaidALL1
111111128-Jun-15FinlandLDPaidALL1
111111129-Jun-15FinlandLDPaidALL1
111111130-Jun-15FinlandLDPaidALL1
111111101-Jul-15FinlandLDPaidALL1
111111102-Jul-15IrelandLDPaidALL1
111111103-Jul-15IrelandLDPaidALL1
111111104-Jul-15IrelandLDPaidALL1
111111105-Jul-15IrelandLDPaidALL1
111111106-Jul-15IrelandLDPaidALL1
111111107-Jul-15IrelandLDPaidALL1
111111108-Jul-15inlandLDPaidALL0.5
111111108-Jul-15inlandPDunpaidALL0.5
111111110-Jul-15inland
111111111-Jul-15inland
111111112-Jul-15inland

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>
 
Upvote 0
Hi Friends,

I Have two Sheets.

Sheet 1 Contains following data;





ID No
Country
Sdate
E Date
Type
Nature
Text Contetns
Days
1111111
Finland
26-Jun-15
1-Jul-15
LD
Paid
ALL
6
1111111
Ireland
2-Jul-15
7-Jul-15
LD
Paid
ALL
6
1111111
Ireland
8-Jul-15
8-Jul-15
LD
Paid
ALL
0.5
1111111
Ireland
8-Jul-15
8-Jul-15
PD
unpaid
ALL
0.5

<tbody>
</tbody>
Sheet2 Contains 2 columns

ID NO Date
1111111
26-Jun-15
1111111
27-Jun-15
1111111
28-Jun-15
1111111
29-Jun-15
1111111
30-Jun-15
1111111
01-Jul-15
1111111
02-Jul-15
1111111
03-Jul-15
1111111
04-Jul-15
1111111
05-Jul-15
1111111
06-Jul-15
1111111
07-Jul-15
1111111
08-Jul-15
1111111
10-Jul-15
1111111
11-Jul-15
1111111
12-Jul-15

<tbody>
</tbody>
Basically I need to check if ID match in both sheet, then I need to match Date in Sheet2 between start date and end date in Sheet1 . If Match found then it should be copied to Sheet 3. In case there is duplicate date in SHeet1 then it should be copied as it is. If Date in SHeet 1 is not in Sheet 2 against matching ID, then it has to copied to 3rd Sheet with ID and date of Sheet2.

After running macro the result should be as follows;



Emp no
Days
Country
Type
Nature
Text Contetns
Days
1111111
26-Jun-15
Finland
LD
Paid
ALL
1
1111111
27-Jun-15
Finland
LD
Paid
ALL
1
1111111
28-Jun-15
Finland
LD
Paid
ALL
1
1111111
29-Jun-15
Finland
LD
Paid
ALL
1
1111111
30-Jun-15
Finland
LD
Paid
ALL
1
1111111
01-Jul-15
Finland
LD
Paid
ALL
1
1111111
02-Jul-15
Ireland
LD
Paid
ALL
1
1111111
03-Jul-15
Ireland
LD
Paid
ALL
1
1111111
04-Jul-15
Ireland
LD
Paid
ALL
1
1111111
05-Jul-15
Ireland
LD
Paid
ALL
1
1111111
06-Jul-15
Ireland
LD
Paid
ALL
1
1111111
07-Jul-15
Ireland
LD
Paid
ALL
1
1111111
08-Jul-15
inland
LD
Paid
ALL
0.5
1111111
08-Jul-15
inland
PD
unpaid
ALL
0.5
1111111
10-Jul-15
inland
1111111
11-Jul-15
inland
1111111
12-Jul-15
inland

<tbody>
</tbody>

Mother India
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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