# Matching two columns with dates

#### aris

##### New Member
Dear friends

I have three columns A,B,C. A and B contain dates (e.g 10/04/1999) and C contains Prices associated with the dates in comumn B . The first and the last date in columns A and B are the same. I would like to match the first column with the second meaning that i want to make column 2 equivalent to column 1. Moreover if a date exists in column A and not column 2 i would like to add this date in column 2 and also add 0 to the corresponding date for Prices. In addition If a date exists in column B and not in column A then just delete date in column B. In other words words we try to match dates with reference to column A. An example is illustrated below.
Data for Menelaos.xls
EFGHIJ
74ABCABC
751995/04/1313/4/1995121995/04/1313/4/199512
761995/04/1414/4/1995171995/04/1414/4/199517
771995/04/1517/4/1995541995/04/1515/4/19950
781995/04/1718/4/1995431995/04/1717/4/199554
791995/04/1819/4/1995341995/04/1818/4/199543
801995/04/1920/4/1995671995/04/1919/4/199534
811995/04/2021/4/1995561995/04/2020/4/199567
821995/04/2124/4/1995451995/04/2121/4/199556
831995/04/2225/4/1995871995/04/2222/4/19950
841995/04/2426/4/1995651995/04/2424/4/199545
851995/04/251995/04/2525/4/199587
861995/04/261995/04/2626/4/199565
Sheet2

Is there any function or way to do this automatically since i have around 2000 observations in my sample?

Thank you very much!!!

Aris

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
aris

Not sure I have understood correctly, but try this (make a backup of your sheet first).

I am referring to your actual column names in your sample not the labels A, B etc that you have added.

1. Insert 2 new columns betwwn columns E and F.
2. In the new cell F75 put the formula =E75.
3. In G75 =IF(ISNUMBER(MATCH(F75,\$H\$75:\$H\$84,0)),VLOOKUP(F75,\$H\$75:\$I\$84,2,0),0) You may need to format the cell as General or Number. Also adjust ranges to suit your data.
4. Select F75:G75 and double click the Fill Handle (small black square at the bottom right of the selected range) This should copy the two columns of formulas and formatting to the bottom of your column E data.
5. While the whole range in columns F & G is selected, choose 'Copy' and then Edit|Paste Special...|Values|OK
6. Delete columns H:I (old data)

Is this anything like what you wanted?

Fantastic Job

Peter

Thank you very much for your help! It is exactly what i wanted and it seems to work fine with a small sample that i tried!

Best

Aris

Replies
4
Views
238
Replies
3
Views
197
Replies
15
Views
412
Replies
2
Views
219
Replies
12
Views
1K

1,206,752
Messages
6,074,725
Members
446,082
Latest member
fgiron83

### 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.

### Which adblocker are you using?

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

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