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 · Sep 26, 2011

You will need the following formula in F1 of Workbook2 (assuming both the workbooks are in xls format and the sheets are Sheet1 for both the files)

=VLOOKUP($C1,[Workbook1.xls]Sheet1!$C:$F,4,FALSE)

Copy paste this formula till the end of Column F where you need values...

mlarson · Sep 26, 2011

Jay, thanks for that. Do you know how I could take that formula and create a macro so that I don't have to copy the formula down all 100,000 rows?

niceguy_jay · Sep 26, 2011

run this code after pasting the formula in F1

Sub paste()
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Cells(1, 6).Select
Selection.Copy
Range("F2:F" & lrow & "").Select
Selection.PasteSpecial
End Sub

niceguy_jay · Sep 26, 2011

You can add the additional codes from here
http://www.mrexcel.com/forum/showthread.php?t=581324
to speed up the execution of process

mlarson · Sep 27, 2011

Jay, that worked very well. Thanks!

mlarson · Sep 29, 2011

Jay, I'm also working on a problem in which I need to use VLOOKUP but it would require more than one column to match. For example, I would need the values in column A, B, H, J and K from Workboook1 to match the same columns in Workbook2. If they do match, I'd like the value in column F from Workbook1 to be in column M in Workbook2. All rows in Workbook2 (the output workbook) that weren't a match can be deleted. Do you know what the macro would look like for this? Thanks!

niceguy_jay · Oct 3, 2011

You can use the following formula in the cell M1 of Workbook2

=IF(AND($A1=[Workboook1.xls]Sheet3!$A1,$B1=[Workboook1.xls]Sheet3!$B1,$H1=[Workboook1.xls]Sheet3!$H1,$I1=[Workboook1.xls]Sheet3!$I1,$J1=[Workboook1.xls]Sheet3!$J1,K1=[Workboook1.xls]Sheet3!$K1),[Workboook1.xls]Sheet3!$F1,"NA")

copy paste the formula till the end of column M..

mlarson · Oct 3, 2011

Jay, apologies for not being clearer. I would like the formula/macro to look at all rows to see if there is a match with columns A,B,H,J,K. For example, 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). Do you know how this would be done and what the formula or macro would look like? Thanks again very much.

niceguy_jay · Oct 3, 2011

How about this

=INDIRECT("[Workbook1.xls]Sheet1!$F"&IF(ISERROR(AND(VLOOKUP($A1,[Workbook1.xls]Sheet1!$A:$A,1,FALSE),VLOOKUP($B1,[Workbook1.xls]Sheet1!$B:$B,1,FALSE),VLOOKUP($H1,[Workbook1.xls]Sheet1!$H:$H,1,FALSE),VLOOKUP($J1,[Workbook1.xls]Sheet1!$J:$J,1,FALSE),VLOOKUP($K1,[Workbook1.xls]Sheet1!$K:$K,1,FALSE))),"NA",MATCH(A1,[Workbook1.xls]Sheet1!$A:$A,0)))

Matching Dates

mlarson

Well-known Member

Excel Facts

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

niceguy_jay

Board Regular

mlarson

Well-known Member

mlarson

Well-known Member

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

Similar threads

Forum statistics

Share this page

Matching Dates

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

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