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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,493
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,795
Messages
5,574,356
Members
412,588
Latest member
FabrizioMaurizio
Top