Compare columns containing dates

[Solved/Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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 
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 :)
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
try again the webpage is

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

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

is it possible to upload it in rapidshare or megaupload ?

Thank you!!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
there was an additional line . I dont know what is this

MD5: 41BFE5E8D81B1ACDFF84E993F65710B6