I am a newbie but fascinated with what Excel VBA can do!
I need help with creating a macro that copies and pastes the first three dates for each symbol to a new sheet(Sheet2).
Below is the data(Sheet1) I have...
A 8/17/2013
A 9/21/2013
A 11/16/2013
A 1/18/2014
A 2/22/2014
A 1/17/2015
AA 8/9/2013
AA 8/17/2013
AA 9/21/2013
AA 10/19/2013
AA 1/18/2014
AA 1/17/2015
AAN 8/17/2013
AAN 9/21/2013
AAN 11/16/2013
AAN 2/22/2014
AAP 8/17/2013
AAP 9/21/2013
AAP 12/21/2013
AAP 1/18/2014
AAP 3/22/2014
AAP 1/17/2015
AAPL 8/9/2013
AAPL 8/17/2013
AAPL 8/23/2013
AAPL 8/30/2013
AAPL 9/6/2013
AAPL 9/21/2013
AAPL 10/19/2013
AAPL 11/16/2013
AAPL 1/18/2014
AAPL 4/19/2014
AAPL 1/17/2015
AAWW 8/17/2013
AAWW 9/21/2013
AAWW 11/16/2013
AAWW 2/22/2014
The problem is I do not want all the symbols from Sheet1. I have specific symbols I want in Sheet2. Also, in sheet2, I already have three rows for each symbol with the symbols names copied and pasted.
So what I want is something like if a symbol in Sheet 1 equals a symbol in Sheet 2 then copy the date but I want the first three dates not the first date repeated 3 times..
A desired sheet2 looks like this
A 8/17/2013
A 9/21/2013
A 11/16/2013
AAWW 8/17/2013
AAWW 9/21/2013
AAWW 11/16/2013
Remember, I have the left column with Symbols already. I need the matching-first three dates for each symbol..
Can anyone help me with this?
I greatly appreciate anyone's help in advance.
Answer
Using your provided sample data, and assuming that you are on Excel 2007 or higher and your data has row 1 as a header row so that actual data starts in row 2, use this formula in 'Sheet2' cell B2 and copy down (you will need to format as date):
=INDEX(Sheet1!$B$2:$B$38,MATCH(1,INDEX((Sheet1!$A$2:$A$38=A2)*(COUNTIFS(A$1:A1,A2,B$1:B1,Sheet1!$B$2:$B$38)=0),),0))
And here is a VBA solution if preferred:
Sub tgr()
Dim cllSymbols As Collection
Dim wsData As Worksheet
Dim wsDest As Worksheet
Dim rngSymbols As Range
Dim SymbolCell As Range
Dim rngFound As Range
Dim arrData() As Variant
Dim varSymbol As Variant
Dim strFirst As String
Dim DataIndex As Long
Dim i As Long
Set cllSymbols = New Collection
Set wsData = Sheets("Sheet1")
Set wsDest = Sheets("Sheet2")
Set rngSymbols = wsDest.Range("A2", wsDest.Cells(Rows.Count, "A").End(xlUp))
If rngSymbols.Row < 2 Then Exit Sub 'No data
On Error Resume Next
For Each SymbolCell In rngSymbols.Cells
If Len(SymbolCell.Text) > 0 Then cllSymbols.Add SymbolCell, SymbolCell
Next SymbolCell
On Error GoTo 0
If cllSymbols.Count > 0 Then
ReDim arrData(1 To cllSymbols.Count * 3)
For Each varSymbol In cllSymbols
Set rngFound = wsData.Columns("A").Find(varSymbol, , xlValues, xlWhole)
If Not rngFound Is Nothing Then
i = 0
strFirst = rngFound.Address
Do
i = i + 1
If i > 3 Then Exit Do
DataIndex = DataIndex + 1
arrData(DataIndex) = wsData.Cells(rngFound.Row, "B").Text
Set rngFound = wsData.Columns("A").Find(varSymbol, rngFound, xlValues, xlWhole)
Loop While rngFound.Address <> strFirst
End If
Next varSymbol
rngSymbols.Offset(, 1).Value = Application.Transpose(arrData)
End If
Set cllSymbols = Nothing
Set wsData = Nothing
Set wsDest = Nothing
Set rngSymbols = Nothing
Set SymbolCell = Nothing
Set rngFound = Nothing
Erase arrData
End Sub
No comments:
Post a Comment