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>

mlarson · Oct 6, 2011

I download the data for each day that evening (not in real-time). So, for 10/6/2011 data I will download it tonight. Unfortunately it can't be downloaded so the date and time is in one column. Is there a way to do it without combining the columns?

mlarson · Oct 6, 2011

Jay, would it help if I combined the Date and Time columns into one column? I could do that with a formula and creating another column.

niceguy_jay · Oct 6, 2011

finally I got it working....first you need to find a spare column say P...and paste this formula in the second row i.e. P2...copy paste it till the last row where you want the values for column M....
EDIT: YOU NEED TO DO THIS IN BOTH THE SHEETS

=A2&B2&H2&J2&K2

then use this formula starting M2 again till the last row of column M

=IFERROR(INDIRECT("[Workbook1.xls]Sheet1!$F"&IF(AND(MATCH($A2,'C:\Users\Jay\Desktop\z\[Workbook1.xls]Sheet1'!$A$1:$A$65536,0),MATCH($B2,'C:\Users\Jay\Desktop\z\[Workbook1.xls]Sheet1'!$B$1:$B$65536,0),MATCH($H2,'C:\Users\Jay\Desktop\z\[Workbook1.xls]Sheet1'!$H$1:$H$65536,0),MATCH($J2,'C:\Users\Jay\Desktop\z\[Workbook1.xls]Sheet1'!$J$1:$J$65536,0),MATCH($K2,'C:\Users\Jay\Desktop\z\[Workbook1.xls]Sheet1'!$K$1:$K$65536,0)),MATCH(P2,[workbook1.xls]Sheet1!$P$1:$P$13,0),"NA")),"NA")

niceguy_jay · Oct 6, 2011

basically what the formula in P2 is doing is forming a unique record which will fetch the row number of the match (column F)....this was the reason why I had asked you for the date column along with HH MM SS so that each record becomes a unique and we can fetch the column...

anyways hopefully the problem is solved with the first formula...

mlarson · Oct 6, 2011

Jay, it works! Thank you so much for your help on this. If you're ever in Northern Virginia the beer is on me. Thank you again!

niceguy_jay · Oct 6, 2011

I am glad that it worked..

Will definitely meet ya someday in Virginia but meanwhile see ya around in the forum..

Matching Dates

mlarson

Well-known Member

mlarson

Well-known Member

Excel Facts

mlarson

Well-known Member

niceguy_jay

Board Regular

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

Board Regular

Similar threads

Forum statistics

Share this page

Matching Dates

mlarson

Well-known Member

mlarson

Well-known Member

Excel Facts

mlarson

Well-known Member

niceguy_jay

Board Regular

niceguy_jay

Board Regular

mlarson

Well-known Member

niceguy_jay

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