Excel: Pattern Matching and printing.

[czarj]

Is there a way in excel to do some semi-automated pattern matching and add a new column as a result? Here are some specifics:

I have two excel files. One contains only two columns and essentially never changes (Masterlist). The second file is a report that is generated daily with different bits of data. However there is one common element between the two files called "SiteID." For each daily report I would like to be able to match the SiteID to the Masterlist and add the second column from the Masterlist to the daily report. Example:

Here is what I have now...

Masterlist:
SiteID, Name
01232, bob
03432, james
12243, silvia
23543, justin

DailyReport:
PI, SiteID, City, DOB
qw, 01232, Morrisville, 1980
sd, 12243, Cary, 1961
gw, 23543, Pittsburgh, 1978
as, 03432, Franklin, 1998

Here is what I would like...

DailyReport:
Name, PI, SiteID, City, DOB
bob, qw, 01232, Morrisville, 1980
silvia, sd, 12243, Cary, 1961
justin, gw, 23543, Pittsburgh, 1978
james, as, 03432, Franklin, 1998

Thanks in advance

--- You must not fight too often with one enemy, or you will teach him all your tricks of war.

 

[SkipVought]

If you look IN your formula in the FORMULA BAR, you will see which reference has been affected.

WHICH ONES?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[czarj]

Everything is referencing back to column A, which now contains a vlookup(#ref) in the formula

--- You must not fight too often with one enemy, or you will teach him all your tricks of war.

 

[SkipVought]

it's interesting that the ORIGINAL formula that you posted in your code references column B. How do you have the lookup in column F when you are inserting column C each time?

What happens if you comment out the code that copies and pastes the formula and run it twice?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[czarj]

If I comment out the code that pastes the formula and run it twice I get two "PI name" columns, nothing more.

I also tried removing the "CurrentRegion" property and just using "entirecolumn." That also caused the #ref (same values) to be put everywhere. What was interesting is it seemed to happen in stages. First, the columns were created and nothing was in them, then a second later the correct data was populated, than after about 1-2 seconds everything became #ref. It was weird, i've never seen that kind of lag before.

--- You must not fight too often with one enemy, or you will teach him all your tricks of war.

 

[SkipVought]

please post the complete code that you are using that results in #REF!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]