Matching two columns with dates

[aris]

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
	E	F	G	H	I	J
74	A	B	C	A	B	C
75	1995/04/13	13/4/1995	12	1995/04/13	13/4/1995	12
76	1995/04/14	14/4/1995	17	1995/04/14	14/4/1995	17
77	1995/04/15	17/4/1995	54	1995/04/15	15/4/1995	0
78	1995/04/17	18/4/1995	43	1995/04/17	17/4/1995	54
79	1995/04/18	19/4/1995	34	1995/04/18	18/4/1995	43
80	1995/04/19	20/4/1995	67	1995/04/19	19/4/1995	34
81	1995/04/20	21/4/1995	56	1995/04/20	20/4/1995	67
82	1995/04/21	24/4/1995	45	1995/04/21	21/4/1995	56
83	1995/04/22	25/4/1995	87	1995/04/22	22/4/1995	0
84	1995/04/24	26/4/1995	65	1995/04/24	24/4/1995	45
85	1995/04/25			1995/04/25	25/4/1995	87
86	1995/04/26			1995/04/26	26/4/1995	65
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

 

[Peter_SSs]

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]

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