Matching two columns with dates

aris

New Member
Joined
Nov 28, 2004
Messages
27
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,259
Office Version
  1. 365
Platform
  1. Windows
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?
 

aris

New Member
Joined
Nov 28, 2004
Messages
27
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,437
Messages
5,831,625
Members
430,077
Latest member
CoulterM

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
Top