Matching Dates

mlarson · Sep 26, 2011

Hi all, I have a moderately difficult question. Well, if you’re really good at this it will probably be easy for you, but it’s difficult for me as I’m not an expert with Excel. I am aware there are other posts on matching but I did not find one that matched in the way I describe below.

I would like to match data from Workbook1 to Workbook2 (each workbook is below). If the date in column C in Workbook1 matches the date in column C in Workbook2, then I want the value in column F of Workbook1 to be placed in column F of Workbook2.

For example, the date in cell C2 (Workbook1) is identical to the dates in cells C2, C3, C4 (Workbook2). Therefore, the value of 1.25 from cell F2 (Workbook1) should be placed in cell F2 of Workbook2 (column F in Workbook2 would be blank prior to the macro being run and placing the appropriate values in those cells).

Is there a macro that can do this easily? My spreadsheet will have roughly 100,000 rows. Thanks in advance for your help.

Workbook1

<table border="0" cellpadding="0" cellspacing="0" width="408"><col style="width: 48pt;" width="64" span="2"> <col style="width: 66pt;" width="88"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 48pt;" width="64">B</td> <td class="xl63" style="width: 66pt;" width="88">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl63">ABCD</td> <td class="xl64">9/1/2010</td> <td class="xl65">464.20</td> <td class="xl65">462.47</td> <td class="xl63">1.25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl63">ABCD</td> <td class="xl64">9/2/2010</td> <td class="xl65">462.87</td> <td class="xl65">461.57</td> <td class="xl63">1.34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl63">ABCD</td> <td class="xl64">9/3/2010</td> <td class="xl65">470.18</td> <td class="xl65">471.78</td> <td class="xl63">1.32</td> </tr> </tbody></table>
Workbook2/Output
<table border="0" cellpadding="0" cellspacing="0" width="408"><col style="width: 48pt;" width="64" span="2"> <col style="width: 66pt;" width="88"> <col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl65" style="width: 48pt;" width="64">B</td> <td class="xl65" style="width: 66pt;" width="88">C</td> <td class="xl65" style="width: 48pt;" width="64">D</td> <td class="xl65" style="width: 48pt;" width="64">E</td> <td class="xl65" style="width: 48pt;" width="64">F</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">2</td> <td class="xl65">ABCD</td> <td class="xl66">9/1/2010</td> <td class="xl68">11:01</td> <td class="xl67">464.20</td> <td class="xl65">1.25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">3</td> <td class="xl65">ABCD</td> <td class="xl66">9/1/2010</td> <td class="xl68">11:02</td> <td class="xl67">464.25</td> <td class="xl65">1.25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">4</td> <td class="xl65">ABCD</td> <td class="xl66">9/1/2010</td> <td class="xl68">11:03</td> <td class="xl67">464.55</td> <td class="xl65">1.25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">5</td> <td class="xl65">ABCD</td> <td class="xl66">9/2/2010</td> <td class="xl68">11:01</td> <td class="xl67">464.30</td> <td class="xl65">1.34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">6</td> <td class="xl65">ABCD</td> <td class="xl66">9/2/2010</td> <td class="xl68">11:02</td> <td class="xl67">465.10</td> <td class="xl65">1.34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">7</td> <td class="xl65">ABCD</td> <td class="xl66">9/2/2010</td> <td class="xl68">11:03</td> <td class="xl67">464.85</td> <td class="xl65">1.34</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">8</td> <td class="xl65">ABCD</td> <td class="xl66">9/3/2010</td> <td class="xl68">11:01</td> <td class="xl67">465.10</td> <td class="xl65">1.32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">9</td> <td class="xl65">ABCD</td> <td class="xl66">9/3/2010</td> <td class="xl68">11:02</td> <td class="xl67">465.04</td> <td class="xl65">1.32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">10</td> <td class="xl65">ABCD</td> <td class="xl66">9/3/2010</td> <td class="xl68">11:03</td> <td class="xl67">465.21</td> <td class="xl65">1.32</td> </tr> </tbody></table>

niceguy_jay · Oct 4, 2011

does the solution work for you ??

mlarson · Oct 4, 2011

I worked on it for about an hour last night and couldn't get it to work. Kept receiving a REF error. Do you know what might be causing that?

niceguy_jay · Oct 4, 2011

are you pasting the formula in cell M2 of workbook2 ?
is the name of workbook1 actually "Workbook1" ?

mlarson · Oct 4, 2011

Yes, I'm pasting it in M2 of Workbook2. The name of Workbook1 is actually Opts - 1.xlsm but I changed that from the formula you provided.

niceguy_jay · Oct 4, 2011

mlarson said:
if row 32 (of Workbook1) matches A,B,H,J,K of row 2 (of Workbook2), then I'd like the value of cell F32 (Workbook1) to be placed in cell M2 (of Workbook2).

when you say row 32 of workbook1, you mean column A,B,H,J,K of workbook1 as well right ?

mlarson · Oct 4, 2011

Yes, that's right. As long as any row in Workbook1 has columns A,B,H,J, and K that matches the same columns in any row in Workbook2. The example was using row 32 from Workbook1 and the formula in row 2 of Workbook2.

niceguy_jay · Oct 4, 2011

now thats wierd cos I have right away tested it with dummy data and is working fine for me..
can you post/upload some sample data where you are getting error..??
thats all I can say and try

mlarson · Oct 4, 2011

Let me keep trying it maybe I'm doing something wrong. Do I need to hit CTRL+SHIFT+ENTER or anything like that?

niceguy_jay · Oct 4, 2011

no nothing like that...just copy paste the formula in normal way and should work...

mlarson · Oct 5, 2011

Jay, unfortunately it still isn't working for me and I have no idea why. Would putting this in a macro possibly work? If you know what that macro would look like I'd like to try that out. Thanks again for your help on this.

Matching Dates

mlarson

Well-known Member

niceguy_jay

Board Regular

Excel Facts

mlarson

Well-known Member

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

mlarson

Well-known Member

Similar threads

Forum statistics

Share this page

Matching Dates

Well-known Member

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock