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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 12, 2010 at 09:36 PM
Related:
- Compare columns containing dates
- Beyond compare - Download - File management
- Display two columns in data validation list but return only one - Guide
- Tweetdeck larger columns - Guide
- Excel compare two sheets - Guide
- Defcon dates - Guide
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
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.
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 12, 2010 at 07:18 AM
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
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!!
is it possible to upload it in rapidshare or megaupload ?
Thank you!!
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 12, 2010 at 09:36 PM
Jul 12, 2010 at 09:36 PM
there was an additional line . I dont know what is this
MD5: 41BFE5E8D81B1ACDFF84E993F65710B6
MD5: 41BFE5E8D81B1ACDFF84E993F65710B6
Jul 12, 2010 at 05:50 AM
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 :)