Compare columns containing dates

Solved/Closed
tonia - Jul 11, 2010 at 11:41 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 12, 2010 at 09:36 PM
Hello,

I have the dates and the close prices of 5 indices in 10 columns. I want to compare the 5 columns containing the dates in order to get the common dates of all the indices and hence the same number of observation for each index. how can I do it in Excel 2007?

Thank you!!!!!

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 12, 2010 at 03:58 AM
you have not given the configuration of your data. In the absence of that I have to guess the configuration. download the file tonia.xls from the following web page.

http://www.speedyshare.com/files/23343606/tonia.xls

in the vbeditor of that file the two macros "test" and Undo are there.

run "test" and see what you get

if you want to recheck
1. run undo
2.run test.


if you need to modify the macro do so Macro are dynamic with as many rows but the indexes are only five

for completion sake the two macro are given below also.


Dim r As Range, c As Range, m As Integer 
Dim j As Integer, k As Integer, ddate(1 To 25) 
Dim rb As Range, cfind As Range, runique As Range 
Dim lastcell As Range 
Sub test() 
m = Range("a1").End(xlDown).Row + 5 

j = 1 
Set r = Range(Range("a2"), Range("a2").End(xlDown).End(xlToRight)) 
For k = 1 To 5 
Set rb = Range("B2:B6").Offset(, (k - 1) * 2) 
'MsgBox rb.Address 

For Each c In rb 
If WorksheetFunction.CountIf(r, c.Value) = 5 Then 
ddate(j) = c.Value 
'MsgBox ddate(j) 

Range("a" & m).Offset(j - 1, 0) = ddate(j) 
j = j + 1 
End If 
Next c 
Next k 
Range("A" & m - 1) = "common dates" 
Set runique = Range(Cells(m - 1, "A"), Cells(Rows.Count, "A").End(xlUp)) 
runique.AdvancedFilter Action:=xlFilterCopy, copytorange:=Cells(Rows.Count, "A").End(xlUp).Offset(5, 0), unique:=True 
Set lastcell = Cells(Rows.Count, "A").End(xlUp) 
Set runique = Range(lastcell, lastcell.End(xlUp).Offset(1, 0)) 
'MsgBox runique.Address 
For Each c In runique 
For k = 1 To 5 
Set rb = Range("B2:B6").Offset(, (k - 1) * 2) 
'MsgBox rb.Address 
Set cfind = rb.Cells.Find(what:=c.Value, lookat:=xlWhole) 
Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(cfind.Row, "A") 
Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(cfind.Row, cfind.Column + 1) 
Next k 
Next c 
Range(Cells(m - 1, "A"), Cells(m - 1, "A").End(xlDown)).EntireRow.Delete 
End Sub 



Sub undo() 
m = Range("a1").End(xlDown).Row 
Set r = Range(Cells(m + 1, "A"), Cells(Rows.Count, "A").End(xlUp)) 
'MsgBox r.Address 
r.EntireRow.Delete 
End Sub 
0
hello,

thank you for replying!! But I cannot open the file as it appears forbidden by the server... I did copy-paste the code on my excel VB but It doesn't work.... :( my data is

A B C D E F G H I J
dates close prices / dates close prices / dates c.p / dates cl. pr / dates cp

for the first index I have 4054 observations, for the second 4049, the third 4030, the forth 4041 and the fifth 4038. I want to find the common dates in a column and keep the corresponding price of each index to the columns next to.

A B(S$P) C(Nikkei) D(DAX) E(FTSE) F(CAC)
dates prices prices prices prices prices

Thanks again for your time :)
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 12, 2010 at 07:18 AM
try again the webpage is

http://www.speedyshare.com/files/23345908/tonia.xls

and then give your comments vis a vis the file
0
https://authentification.site/files/23346258/tonia3.xls

is it possible to upload it in rapidshare or megaupload ?

Thank you!!
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 12, 2010 at 09:36 PM
there was an additional line . I dont know what is this

MD5: 41BFE5E8D81B1ACDFF84E993F65710B6
0