autofilter copy paste and lookup

[JeroenS]

Hello,

I am trying to automate a process but don?t know how to do it in a macro for I am a beginner at using macro's but willing to learn :) . I have looked at the site but did not find any macro that could assist me with part of my problem. I added an example file where I did the filtering manually and pasting it to the new sheet so you can see what I mean.

In the window you see my imported data from crsp-database. It is only a part of it. The real database contains 8040 funds.

What I want it to do is looking in column A with the icdi codes, which are unique. It should start with selecting the first ICDI code and then copy the name and ticker to paste into a new sheet for example sheet: ?monthly return?. It must take the last name, cause funds can change name. So for the fund with ICDI code 1981 I want to have copied AIM and not Invesco.
After that it has to copy all the returns from column C that have the same icdi code and paste them into the sheet ?monthly returns?. But when it pastes the return it should consider the dates and match them. After that the whole process should start over again.


I hope somebody knows how to tackle this problem or give me any advice how to start such a macro, I would appreciate it cause doing it manually would take very long.

[Shazam]

I read your post and its a little confusing. But I came up with a formula That I think I know what you are looking for. Is this what you are looking for ?

Put this formula in worksheet "Monthly Return" cell B6 and copy it down to your desire.


=IF(ISNA(INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=A6)*(Im port!$E$2:$E$621='Monthly return'!$B$3),0,1),0))),"",INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=A6)*(Import!$E$ 2:$E$621='Monthly return'!$B$3),0,1),0)))

Or a shorter version:

=INDEX(Import!$C$2:$C$619,MATCH(A6&B$3,Import!B$2:B$600&Import!E$2:E$600,0))

Its an a array so you need to do is:

CTRL+SHIFT+ENTER

[JeroenS]

You are already helping me a lot shazam :bow: and yes it is what I am looking for. I had to change the code a little bit for my problem.

The problem is that if you look at the fund with ICDI code 1981 the funds name changes it's name 3 times first it is INVESCO Advisor: Real Estate Fund and then it is renamed to AIM Advisor: Real Estate Fund/C and even a third change.

So I changed the code a little bit to look at the ICDI codes.

So from your code:
=IF(ISNA(INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=A6)*(Im port!$A$2:$A$621='Monthly return'!$B$2),0,1),0))),"",INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=A6)*(Import!$A$ 2:$A$621='Monthly return'!$B$2),0,1),0)))

I changed it to:
=IF(ISNA(INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=$A6)*(I mport!$A$2:$A$621='Monthly return'!B$2),0,1),0))),"",INDEX(Import!$C$2:$C$619,MATCH(1,INDEX((Import!$B$2:$B$619=$A6)*(Import!$A $2:$A$621='Monthly return'!B$2),0,1),0)))

I had to put in a $-sign in front of A6 and removed it from $B$2. That way I can also copy it sideways

Still there are 3 things left I would like advice with. I hope I am more clear now what I would like.
First if you look at the example file you see in the import sheet that is has an empty cell at C2 and C117 and with the code being used it puts in a 0 at the "Monthly Return" Sheet if the cell is blank. Is there a way to prevent that from happening so to keep it an empty cell. I did a quick #NA in the empty cells from the import sheet so it copies itself to the Monthly return sheet. Then I have to do a find/replace to remove the #NA. I wonder if this can be incorporated in the formula.

Second thing is I still need to copy and paste the ICDI code, the fund name and tickercode manually into the "Monthly Return" sheet. Is there a way to look at the ICDI codes for the last entry of every ICDI Code and then copy the ICDI code, the fund name and the tickercode of the corresponding row.
So to give an example for code 701 It has to go to cell a116 of the import sheet copy A116, E116 and F116 and paste them into the Monthly return sheet at B2 to B4. If that could be possible that would really be great.

Third is you made and index to look up till C619. My importfiles are of different lenghts. The simple way is to set it to all the way down to the last cell of C65536, but wouldn't that make the calculation time a lot longer? So could it be possible to make a countformula that looks at column A and gives the total number of rows and then implement it in the index formula like this
=IF(ISNA(INDEX(Import!$C$2:$C"&G1&"..........
Where G1 is the countformula cell that looks at the entries of column A

And may I thank you for what you have done already!

[Shazam]

I'm Here at work I'll see what I could do later on this evening.

[JeroenS]

Ok i was busy with the second part of my problem for copy pasting the names and tickers and I am nearly done with writing the macro. So if you have an answer to the 1st and 3th problem that would be nice.

In the end I want it to have macro that can do it all by just clicking once:cloud9:

[Shazam]

Ok I think I got everything. I did 2 codes. The reason why its all depends which one is faster for you. The codes implement the formulas down to the coresponding active cells in column A. I dont know how fast is your computer But when you run these codes it will look like it freezes dont worry it's implementing your formulas. Got to remember there is 65536 rows and the new Excel 12 is comming out you will have to adjust the number of rows in the code to 1.1 million if you want to. Here are the 2 codes. I attach the file below with the code in the workbook. They are stored in the modules.

If there somebody out there could make these codes faster you'll welcome to do it because I'm want to know how to make them faster also.

Test1

Sub Test1()
Dim lrow As Long
Dim sh As Worksheet

Sheets("Import").Select
Range("A2").Select
For Each c In ActiveSheet.UsedRange
If c.Value = "0" Then c.Value = "'"
c.HorizontalAlignment = xlCenter
Next c

Set sh = Sheets("Monthly Return")
Application.ScreenUpdating = False
With sh
lrow = .Range("a65536").End(xlUp).Row
.Application.Calculation = xlCalculationManual
With .Range("b6:b" & lrow)
.FormulaR1C1 = "=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1" & _
"='Monthly return'!R2C),0,1),0)))"
End With

With .Range("c6:c" & lrow)
.FormulaR1C1 = "=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
End With

With .Range("d6:d" & lrow)
.FormulaR1C1 = "=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
End With

With .Range("e6:e" & lrow)
.FormulaR1C1 = "=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
End With


.Application.Calculation = xlCalculationAutomatic
End With
Range("a1").Select
Application.ScreenUpdating = True
End Sub


My Opion is Test2 is faster.

Test2

Sub Test2()
Application.ScreenUpdating = False
Dim x As Long
Sheets("Import").Select
Range("A2").Select
For Each c In ActiveSheet.UsedRange
If c.Value = "0" Then c.Value = "'"
c.HorizontalAlignment = xlCenter
Next c

Sheets("Monthly return").Select
Application.Calculation = xlCalculationManual
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R65536C3,MATCH(1," & _
"INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R65536C3," & _
"MATCH(1,INDEX((Import!R2C2:R65536C2=RC1)*(Import!R2C1:R65536C1=" & _
"'Monthly return'!R2C),0,1),0)))"

x = Range("A65536").End(xlUp).Row
Range("B6:B" & x).FillDown
Range("C6:C" & x).FillDown
Range("D6http://vbaexpress.com/forum/images/smilies/astrosmiley.gif" & x).FillDown
Range("F6:F" & x).FillDown
Range("E6:E" & x).FillDown

Application.Calculation = xlCalculationAutomatic

Range("A1").Select
Application.ScreenUpdating = True
End Sub

[JeroenS]

I am looking at the code and I think a loop would be good here, because the formula just shifts a column.

I see you implemented code to look for "0" and then replace it with " ' ". In this case i think it should look for empty cells as "" cause sometimes the actual return is 0%. So in that case it must not remove it.

And don't worry about my PC its p4 3ghz with 1gig internal :)

[geekgirlau]

Hi Shazam,

I've edited your code to put some line breaks in the formulas - it makes it a lot easier to read!

[Shazam]

Thanks geekgirlau I got to get into the habit of doing that.

[JeroenS]

I have done some tweaking to the macro already. It is a lot faster and I added some extra things to it. I hope I get it done in 2 hours and then poste it here.

[JeroenS]

Ok this is how far I came.

It now fills in the entire Monthly return sheet. Still I think the code can be made a lot faster. So if anybody has an idea how to do it that would be great. Sorry if the code is sometimes confusing, for I am not an everyday coder but I hope its clear about what it does. I also include another file with the macro in it.

Still what I would also like to let it do is check if the sheet "monthly return" exists and if so continue with the code under here, else create the worksheet "Monthly return" and continue with the underneath code



Sub Test2faster()
'Allthough this macro still looks all the way down to the final cell it only does it once.
'So it should make it faster.
'It also copies the ICDI Code, Fund name and Ticker code to the "monthly return" sheet.

'So that screen will only update when all calculations have been done.
Application.ScreenUpdating = False
'This makes the looping able to look at how many funds there really are.
'This formula can only take values into account not text
Sheets("Import").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "Total unique number of funds"
Range("I3").Select
ActiveCell.FormulaR1C1 = _
"=SUM(N(FREQUENCY(C[-8],C[-8])>0))"

'This is used to create the layout of the sheet "Monthly return"
Sheets("Monthly Return").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Monthly Return"
Range("A2").Select
ActiveCell.FormulaR1C1 = "ICDI"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Ticker"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A6").Select
ActiveCell.FormulaR1C1 = "1/31/1990"
Range("A7").Select
ActiveCell.FormulaR1C1 = "2/28/1990"
Range("A6:A7").Select
Selection.AutoFill Destination:=Range("A6:A185"), Type:=xlFillDefault
Range("A6:A185").Select
Columns("A:A").EntireColumn.AutoFit

'Fits the column to the text.
Columns("H:H").EntireColumn.AutoFit
'Setting variables
Dim a As Integer
Dim b As Integer
Dim x As Long
Dim Looping As Integer
'startvalue for variables.
a = 2
b = 2
'Here there will be searched in the ICDI codes of "Import" Sheet Column A for a change 1st loop
Sheets("Import").Select
For Looping = 1 To Cells(3, 9)

'2nd loop
Do While Cells(a, 1) = Cells(a + 1, 1)
Cells(a + 1, 1).Select
a = a + 1
Loop

'If a change is found here it copies the last entry before the ICDI code changes
'and pastes the ICDI code to the "monthly return" Sheet.
Cells(a, 1).Select
Selection.Copy
Sheets("Monthly Return").Select
Cells(2, b).Select
ActiveSheet.Paste
'Same copy and pasting but this is for the fund name and ticker code
Sheets("Import").Select
Range(Cells(a, 5), Cells(a, 6)).Select
Selection.Copy
Sheets("Monthly Return").Select
Cells(3, b).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
b = b + 1
a = a + 1
'after copy and pasting is done it selects the next icdi code and the whole process starts over.
Sheets("Import").Select
Cells(a, 1).Select
Next

'Further changing of the layout of the "Monthly Return" Sheet
Sheets("Monthly Return").Select
Rows("1:5").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("B6:IV185").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"


'Empty cells will be checked and if empty given 'sign
Sheets("Import").Select
Range("A2").Select
For Each c In ActiveSheet.UsedRange
If c.Value = "" Then c.Value = "'"
c.HorizontalAlignment = xlCenter
Next c

'Resetting for variable a this tells how far the formula should be copied to the right based on the
'number of unique funds
a = 0

'setting x for the "import" sheet This way we do not have to look at cells up to 65536
'anymore but only those cells that are really being filled with data.
x = Range("A65536").End(xlUp).Row

For Looping = 1 To Cells(3, 9)
Sheets("Monthly return").Select
Application.Calculation = xlCalculationManual
Cells(6, 2 + a).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R" & x & "C3,MATCH(1,INDEX((Import!R2C2:R" & x & "C2=RC1)*(Import!R2C1:R" & x & "C1='Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R" & x & "C3,MATCH(1,INDEX((Import!R2C2:R" & x & "C2=RC1)*(Import!R2C1:R" & x & "C1='Monthly return'!R2C),0,1),0)))"
a = a + 1
Next

'Setting x for the "monthly return" sheet
x = Range("A65536").End(xlUp).Row
Range(Cells(6, 2), Cells(x, 2 + a)).FillDown
Application.Calculation = xlCalculationAutomatic

Application.Goto reference:="R1C1"
Application.ScreenUpdating = True

End Sub

[Shazam]

This code will check if worksheet "Monthly return" does not exist it will create a worksheet name "Monthly return"



Sub Test2faster()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "Monthly return" Then ws.UsedRange.Clear
Next ws
On Error Resume Next
If IsError(Sheets("Monthly return").Select) Then Sheets.Add
ActiveSheet.Name = "Monthly return"
On Error GoTo 0
'Allthough this macro still looks all the way down to the final cell it only does it once.
'So it should make it faster.
'It also copies the ICDI Code, Fund name and Ticker code to the "monthly return" sheet.

'So that screen will only update when all calculations have been done.
Application.ScreenUpdating = False
'This makes the looping able to look at how many funds there really are.
'This formula can only take values into account not text
Sheets("Import").Select
Range("H3").Select
ActiveCell.FormulaR1C1 = "Total unique number of funds"
Range("I3").Select
ActiveCell.FormulaR1C1 = _
"=SUM(N(FREQUENCY(C[-8],C[-8])>0))"

'This is used to create the layout of the sheet "Monthly return"
Sheets("Monthly Return").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Monthly Return"
Range("A2").Select
ActiveCell.FormulaR1C1 = "ICDI"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Name"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Ticker"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A6").Select
ActiveCell.FormulaR1C1 = "1/31/1990"
Range("A7").Select
ActiveCell.FormulaR1C1 = "2/28/1990"
Range("A6:A7").Select
Selection.AutoFill Destination:=Range("A6:A185"), Type:=xlFillDefault
Range("A6:A185").Select
Columns("A:A").EntireColumn.AutoFit

'Fits the column to the text.
Columns("H:H").EntireColumn.AutoFit
'Setting variables
Dim a As Integer
Dim b As Integer
Dim x As Long
Dim Looping As Integer
'startvalue for variables.
a = 2
b = 2
'Here there will be searched in the ICDI codes of "Import" Sheet Column A for a change 1st loop
Sheets("Import").Select
For Looping = 1 To Cells(3, 9)

'2nd loop
Do While Cells(a, 1) = Cells(a + 1, 1)
Cells(a + 1, 1).Select
a = a + 1
Loop

'If a change is found here it copies the last entry before the ICDI code changes
'and pastes the ICDI code to the "monthly return" Sheet.
Cells(a, 1).Select
Selection.Copy
Sheets("Monthly Return").Select
Cells(2, b).Select
ActiveSheet.Paste
'Same copy and pasting but this is for the fund name and ticker code
Sheets("Import").Select
Range(Cells(a, 5), Cells(a, 6)).Select
Selection.Copy
Sheets("Monthly Return").Select
Cells(3, b).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
b = b + 1
a = a + 1
'after copy and pasting is done it selects the next icdi code and the whole process starts over.
Sheets("Import").Select
Cells(a, 1).Select
Next

'Further changing of the layout of the "Monthly Return" Sheet
Sheets("Monthly Return").Select
Rows("1:5").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("B6:IV185").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Selection.NumberFormat = "0.00%"


'Empty cells will be checked and if empty given 'sign
Sheets("Import").Select
Range("A2").Select
For Each c In ActiveSheet.UsedRange
If c.Value = "" Then c.Value = "'"
c.HorizontalAlignment = xlCenter
Next c

'Resetting for variable a this tells how far the formula should be copied to the right based on the
'number of unique funds
a = 0

'setting x for the "import" sheet This way we do not have to look at cells up to 65536 anymore but only those cells
'that are really being filled with data.
x = Range("A65536").End(xlUp).Row

For Looping = 1 To Cells(3, 9)
Sheets("Monthly return").Select
Application.Calculation = xlCalculationManual
Cells(6, 2 + a).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(INDEX(Import!R2C3:R" & x & "C3,MATCH(1," & _
"INDEX((Import!R2C2:R" & x & "C2=RC1)*(Import!R2C1:R" & x & "C1=" & _
"'Monthly return'!R2C),0,1),0))),"""",INDEX(Import!R2C3:R" & x & "C3," & _
"MATCH(1,INDEX((Import!R2C2:R" & x & "C2=RC1)*(Import!R2C1:R" & x & "C1=" & _
"'Monthly return'!R2C),0,1),0)))"
a = a + 1
Next

'Setting x for the "monthly return" sheet
x = Range("A65536").End(xlUp).Row
Range(Cells(6, 2), Cells(x, 2 + a)).FillDown
Application.Calculation = xlCalculationAutomatic

Application.Goto reference:="R1C1"
Application.ScreenUpdating = True

End Sub