Hi,
I have 2 worksheets with intraday stock data containing the followin
data:
price, and deal hour (in minutes).
I want to compare the two worksheets and put in third worksheet all th
deals that were made in the same minute.
for example:
first work sheet
time price
10:01 100
10:05 101
10:07 102
10:10 103

second work sheet
time price
10:02 100
10:03 101
10:05 102
10:06 103
10:07 103

so i want excel to put in the third worksheet all the deals made in th
same minute:

time price
10:05 101
10:07 102

thank

--
kma
-----------------------------------------------------------------------
kman's Profile: http://www.excelforum.com/member.php...nfo&userid=333
View this thread: http://www.excelforum.com/showthread.php?threadid=48794

try this macro

Sub macro1()
'need to define ranges on sheets 1 and 2
'define row range of sheet 1 data and sheet 2 data
sheet1startrow = Range("sheet1data").Row
sheet1endrow = Range("sheet1data").End(xlDown).Row
sheet2startrow = Range("sheet2data").Row
sheet2endrow = Range("sheet2data").End(xlDown).Row
'define matching times
Dim atime(100) As Double
Dim thistime As Double
For i = sheet1startrow To sheet1endrow
thistime = Sheets("sheet1").Cells(i, 1).Value
For h = sheet2startrow To sheet2endrow
If thistime = Sheets("sheet2").Cells(h, 1).Value Then GoTo found
Next h
GoTo nexttime
found:
'store times that match
j = j + 1
atime(j) = thistime
nexttime:
Next i
If j = 0 Then GoTo theend
'place data in columns 1 and 2 on sheet 3 beginning in row 3
For k = 1 To j
Sheets("sheet3").Cells(k + 2, 1).Value = atime(k)
Sheets("sheet3").Cells(k + 2, 2).FormulaR1C1 = _
"=VLOOKUP(RC[-1],Sheet1data,2,FALSE)"
Next k
theend:
End Sub


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=487948

Try this Add-in (Compare)
http://www.cpearson.com/excel/download.htm

This Add-In utility allows you to compare the contents of two workbooks. This was written by Myrna Larson and Bill Manville.


--
Regards Ron de Bruin
http://www.rondebruin.nl


"kman" wrote in message
...

Hi,
I have 2 worksheets with intraday stock data containing the following
data:
price, and deal hour (in minutes).
I want to compare the two worksheets and put in third worksheet all the
deals that were made in the same minute.
for example:
first work sheet
time price
10:01 100
10:05 101
10:07 102
10:10 103

second work sheet
time price
10:02 100
10:03 101
10:05 102
10:06 103
10:07 103

so i want excel to put in the third worksheet all the deals made in the
same minute:

time price
10:05 101
10:07 102

thanks


--
kman
------------------------------------------------------------------------
kman's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread...hreadid=487948