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]

hi,

That's a simple VLOOKUP() function.

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

 

[czarj]

Yes, i'm sure VLOOKUP is indeed the correct function. What i need help with is the "semi-automated" part. How do I create a macro that opens the second file, creates the table and inserts the vlookup function as a new column in the spreadsheet?

Thanks!


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

 

[SkipVought]

Setting up this lookup function is a ONE TIME event, requiring no macro.

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

 

[Gruuuu]

Set up a new sheet in the file that has your master list, in that new sheet, INSERT a TABLE with 5 Columns and 1 Row, make sure to select 'My Table has headers'.
Name the columns as you wish.
In the first column (assumedly name), put in your VLOOKUP().
Record a macro of you opening your file, and pasting the values of your daily report starting from Row 2 Column 2 of your table, and then copying that table's values to a new document, and saving that document off. Also within the recorded macro, close and DO NOT SAVE the master list file. Stop recording the macro here.

Tweak the macro in whatever way you need to get it to do what you need. If you need help tweaking your macro, post on this forum:

http://www.tek-tips.com/threadminder.cfm?pid=707

and remember to include your VBA code in the post, as well as what you want your code to do, and what problems you're having in not being able to make it do what you want to do.

Sound good?

 

[SkipVought]

To get data from a table in another workbook, simply use MS Query. This query can simply be refreshed when needed. No need for a Copy 'n' paste macro.

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

 

[czarj]

This really is a multi-step process that requires a macro. The steps are this:

(1) Create a new column in "dailyreport"
(2) label the column
(3) determine which rows in the column need the lookup function (or at least where to end)
(4) insert the approprite lookup in the columns from #3

This works for #1 and #2:

Sub CreateColumn()
    Columns(3).Insert

    Range("C4").Select
    ActiveCell.FormulaR1C1 = "P.I. Name"
    With ActiveCell.Characters(Start:=1, Length:=9).Font
        .Name = "Tahoma"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 4
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("C5").Select
    
End Sub

This works for #4:

=VLOOKUP(B5,'c:\[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)

The question becomes how to I perform #3 and what needs to be edited in the VLOOKUP for automatic pasting via a macro?

Thanks!

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

 

[SkipVought]

Create a new column in "dailyreport"

Is the new column ALWAYS column 4?

Sub CreateColumn()
    Columns(3).Insert
    
    With Range("C4")
        .Value = "P.I. Name"
        With .Font
            .Name = "Tahoma"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 4
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        
        With .Offset(1)
            .Formula = "=VLOOKUP(B5,'c:\[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)"
            .Copy
    
            Intersect(.EntireColumn, .CurrentRegion, Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))).PasteSpecial xlPasteFormulas
    
        End With
    End With
    
End Sub

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

 

[czarj]

New column is always in the same place, yes. Columns(3).Insert puts it in the 3rd position ("C").

That code works except it doesn't necessarily get lookups in every needed column. Sometimes these reports have blank lines between sections. Is there a way to specify if the corresponsing cell in column 2/B has a value, then insert the lookup in 3/C?

Thanks!

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

 

[SkipVought]

Then either loop thru each row in your report or use the AutoFilter to display the 'blank lines between sections' to delete unneeded data in this column.

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

 

[SkipVought]

TILT!

Why would you even consider having BLANK ROWS in a table or report? Along with MERGE CELLS, BLANK ROWS are a scourge to Excel functionality, causing death and distruction, as it were.

Get rid of them!!!

Use Row Height and Vertical alignment instead to give space in reports!

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

 

[czarj]

I agree with the blank rows statement, but it is not up to me. This silly report is generated from an ancient database that I do not have access to. These are generated for an entire department on a daily basis. I am just trying to make up for the lack of technological initiative available to me.

Since I can't use any "ideal solutions" i'm trying to figure out if I can insert the lookup based on two conditions in the previous column - E.G. the cell in column B contains data and has NO shading. Some rows are blank and there are a couple section headers. If I can insert based on this critieria this will be perfect.

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

 

[SkipVought]

Then modify my solution...

        With .Offset(1)
            .Formula = "=VLOOKUP(B5,'c:\[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)"
            .Copy
    
            Intersect(.EntireColumn, [b].Parent.UsedRange[/b], Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))).PasteSpecial xlPasteFormulas
    
        End With

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

 

[czarj]

Hmmm....

The ".Parent.UsedRange" change seems to make everything freak out. Each cell in the entire spreadsheet is trying to use the lookup reference.

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

 

[SkipVought]

Let me explain...

Intersect(.EntireColumn, .Parent.UsedRange, Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))).PasteSpecial xlPasteFormulas

this takes the intersection of...
[tt]
1. COLUMN C
2. the Used Range (all the cells that have stuff, and not just data)
3. all the cells from C5 to the bottom of the sheet.
[/tt]
In my test, I have data in rows 1 thru 14 of some other column. Some of the rows are BLANK.

My result is the formula in C5 to C14.

That's what happens.

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

 

[czarj]

For this dataset is takes everything from A5-G80 and pastes the vlookup function in each cell. This is the entire worksheet expect the header.

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

 

[SkipVought]

yeah, it could really be shortened to...

Intersect(.Parent.UsedRange, Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))).PasteSpecial xlPasteFormulas

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

 

[czarj]

Thanks, but that does the same thing. The results are a #REF in every cell in the sheet.

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

 

[SkipVought]

and looking in your formulas, what reference has been destroyed?

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

 

[czarj]

It simply overwrites all the existing fields of text with the vlookup reference. Example:

Before:
Disposition
Disposition
Disposition
Disposition
Disposition

After:
#REF (=VLOOKUP(F5,'[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)
#REF (=VLOOKUP(F6,'[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)
#REF (=VLOOKUP(F7,'[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)
#REF (=VLOOKUP(F8,'[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)
#REF (=VLOOKUP(F9,'[Center List.xls]Sheet1'!A$2:B$350,2,FALSE)



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