I have an excel file that has a sheet with pipeline information on loans (sheet name is "Pipeline"); there is a unique identifier on column D (loan number) in that sheet. In that same excel file but a different sheet called "Projections" that same unique identifier (loan number) is on column "D", there are 4 other columns with data that I would like to copy over to the Pipeline sheet from the Projections sheet based on the loan number match. I would like the macro to match the loan # on column D (range is D2:D600) from Pipeline sheet to column D in Projection sheet (same range) and if found I would like to copy the contents of columns I, J, K and L from the Projections sheet to the same columns in the Pipeline sheet for each loan number that matches.
I honestly don't know where to start, I usually use the macro recorder and tweak from there but the macro recorder doesn't really help you create the logic, can anyone help me?
Why don't you just use VLOOKUP? The basic formula for Pipeline!I2 would be as follows. Drag this down as far as required.
=VLOOKUP($D2,Projections!$D$2:$L$600,6,0)
For Pipeline!J2 use =VLOOKUP($D2,Projections!$D$3:$L$600,7,0)
To expand on this a bit, you could put this in Pipeline!I2 and drag it both down and across to Column L.
=VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0)
Finally, to eliminate the #N/A error that will occur if a value isn't found on the Projections sheet, put this in Pipeline!I2 and drag it down and across.
=IFNA(VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0),"Not Found")
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03
I overwrite the pipeline data everyday and would have to be mindful of these columns. Once I hand this off to my admin I am sure there will be mistakes in overwriting the formula so I thought VBA code would take out the human error out if it.
I see your point. Two options, both of which eliminate to possibility of human error:
1 - Write multiple lines of code to find and copy the data.
2 - Write a single line of code to insert the VLOOKUP formula.
Sub InsertVLOOKUP_ISNA() Sheets("Pipeline").Range("I2:L600").Formula = _ "=IF(ISNA(VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0)),""Not Found""," & _ "VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0))" End SubYou may have noticed that I switched from IFNA to IF(ISNA). That's because I forgot that IFNA is not backward compatible to versions of Excel before 2013.
Here is the IFNA code which will work in Excel 2013 and beyond.
Sub InsertVLOOKUP_IFNA() Sheets("Pipeline").Range("I2:L600").Formula = _ "=IFNA(VLOOKUP($D2,Projections!$D$2:$L$600,COLUMN()-3,0),""Not Found"")" End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03
DerbyDad this worked and is a better solution than option 1, 2 requests:
1. the macro works with visible cells only, my totals weren't adding up and then I realized i had a filter on and so the formulas did not copy to the filtered out cells; can we force it to copy to all cells even if they are filtered out?2. Is there a way to convert the formula to it's value when completed instead of leaving the formula in the cell?