Vlookup and Combining

R

ritesh_akh

New Member
Joined
May 3, 2011
Messages
11
Hi Masters,

This forum has been of real help to me in solving some of my big time project works. Today i need one more help from you all.

I have two excel workbooks named as "Names" and "Vlook"

The Names and the Vlook workbook contains some data in the following format:

Workbook_Look


I need a code to do the following:
- From the workbook named "Names" it should get the company name, matches it from the workbook named "Vlook". If the company name matches then in the workbook named "Name" column E2 it should paste the value from the workbook named "Vlook" column next to where the company name matched.

For eg:
If in workbook "Names" column D4 has the company name "Astound"
It searched the company name and matches it from workbook "Vlook" Column A.
If in any row it finds the same name for example say in column A16 then in the workbook "Names" column E4 should get the value of workbook "Vlook" column B16 and column F4 of workbook "Names" should get the value of column C16 of workbook "Vlook"

Take a look:

Vlook


Another look:

Vlook1


Now once the values are placed in the column E and column F of workbook "Names" from the workbook "Vlook", i want to do the following :

Final


Please help me on this.

Thanks in advance.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
I assumed the VLOOK workbook's data really started in the first row. If that is true, copy the following formulas to E4 through G4, then copy them down for the other rows:

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 58px"><COL style="WIDTH: 73px"><COL style="WIDTH: 118px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>first.last</TD><TD>ast</TD><TD>Axe.Juliet</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E4</TD><TD>=VLOOKUP(D4,[Vlook.xlsx]Sheet1!$A$1:$C$6,2,0)</TD></TR><TR><TD>F4</TD><TD>=VLOOKUP(D4,[Vlook.xlsx]Sheet1!$A$1:$C$6,3,0)</TD></TR><TR><TD>G4</TD><TD>=IF(E4="first.last",B4&"."&C4,IF(E4="firstl",B4&LEFT(C4,1),IF(E4="flast",LEFT(B4,1)&C4,IF(E4="firstlast",B2&C2))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Mike
 
Upvote 0
Please help me on this. Please !

If its difficult to combine the both things then making it separate codes will also work but please help.
 
Upvote 0
I would write a macro that put those formulas in the workbook Names and then copy/paste Values the values of the formulas.

What code have you tried?
 
Upvote 0
mikerickson said:
I would write a macro that put those formulas in the workbook Names and then copy/paste Values the values of the formulas.

What code have you tried?
Click to expand...

Hi,

I have just used the formulas that were pasted by Mike.

Once i did that i copied the values in notepad to remove the formulas and then pasted it back in the excel.

That's all i did. No other code, as i don't know how to write a macro.
 
Upvote 0
Since you don't know VBA, you are not going to be able to maintain a VBA routine.
Plus the fact that, in general, VBA is inferior (slower, more prone to error conditions, more memory,....) to native Excel.

The formula based solution above is the best way to go.

But if you insist:
This macro should do what you want, after the ranges are adjusted to meet your situation.
Code: 
Sub test()
    Dim namesRange As Range
    Dim companyRange As Range
    
    With Workbooks("Names.xlsm").Sheets("Sheet1").Range("A:A")
        Set namesRange = Range(.Cells(2, 4), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    With ThisWorkbook.Sheets("Sheet2").Range("A:A")
        Set companyRange = Range(.Cells(1, 3), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With namesRange.Offset(0, 4).Resize(, 3)
        .Rows(1).FormulaR1C1 = Array("=VLOOKUP(RC4," & companyRange.Address(True, True, xlR1C1, True) & ",2,FALSE)" _
                                    , "=VLOOKUP(RC4," & companyRange.Address(True, True, xlR1C1, True) & ",3,FALSE)" _
                                    , "=CHOOSE(MATCH(RC[-2],{""first-last"",""first.last"",""firstl"",""firstlast"",""flast""}),RC2&""-""&RC3,RC2&"".""&RC3,RC2&LEFT(RC3,1),RC2&RC3,LEFT(RC2,1)&RC3)")
        .FillDown
        .Value = .Value
    End With
    
End Sub
 
Upvote 0
Or do the following (for two workbooks):

Note: I used the names Company01 and Company to the workbook and worksheet masters respectively. Additionally, I used the names Company02 and CompanyAux to the workbook and worksheet helpers respectively.

Workbook - Company01
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">First Name</TD><TD style="FONT-WEIGHT: bold">Last Name</TD><TD style="FONT-WEIGHT: bold">Company Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Format</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dashboard</TD><TD style="FONT-WEIGHT: bold">Front Desk</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Axe Juliet</TD><TD>Axe</TD><TD>Juliet</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>Axe.Juliet-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Rob Thomson</TD><TD>Rob</TD><TD>Thomson</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>Rob-Thomson-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Christiana Roberts</TD><TD>Christiana</TD><TD>Roberts</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>ChristianaRoberts-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Julie Valentine</TD><TD>Julie</TD><TD>Valentine</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>JValentine-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Alec Mac</TD><TD>Alec</TD><TD>Mac</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>AlecM-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>First01 Last01</TD><TD>First01</TD><TD>Last01</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First01Last01-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>First02 Last02</TD><TD>First02</TD><TD>Last02</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>First02L-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>First03 Last03</TD><TD>First03</TD><TD>Last03</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>First03L-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>First04 Last04</TD><TD>First04</TD><TD>Last04</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>First04-Last04-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>First05 Last05</TD><TD>First05</TD><TD>Last05</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First05Last05-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>First06 Last06</TD><TD>First06</TD><TD>Last06</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>FLast06-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>First07 Last07</TD><TD>First07</TD><TD>Last07</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>First07-Last07-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>First08 Last08</TD><TD>First08</TD><TD>Last08</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>FLast08-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>First09 Last09</TD><TD>First09</TD><TD>Last09</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>First09.Last09-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>First10 Last10</TD><TD>First10</TD><TD>Last10</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>First10.Last10-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>First11 Last11</TD><TD>First11</TD><TD>Last11</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First11Last11-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>First12 Last12</TD><TD>First12</TD><TD>Last12</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First12Last12-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>First13 Last13</TD><TD>First13</TD><TD>Last13</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First13Last13-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>First14 Last14</TD><TD>First14</TD><TD>Last14</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First14Last14-ast</TD></TR></TBODY></TABLE>Sheet - Company



Workbook - Company02
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Company Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Format</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dashboard</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD></TR></TBODY></TABLE>
Sheet - CompanyAux



Important: do all tests initially using the same layouts of workbooks Company01 and Company02.

Copy the code below.

Code: 
Sub [COLOR=blue]VlookupComb[/COLOR]()
'
'Prg    : VlookupComb
'Author : Markmzz
'Date   : 19/06/2011
'Version: 01
 
    'Define the macros variables
    Dim myWk01, myWk02 As Workbook
    Dim mySheet01, mySheet02 As Worksheet
    Dim myWk01path As String
    Dim LastCol01, LastRow01 As Long
    Dim LastCol02, LastRow02, i As Long
    Dim myCell As Range
 
    'Initializes the error routine
    On Error GoTo ErrorHandler
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = False
 
    'Store the reference of path the of the master
    'workbook (Company01) in the variable myWk01path
    myWk01path = ThisWorkbook.Path & "\"
    'Store the reference of the master workbook in the
    'variable myWk01
    Set myWk01 = ActiveWorkbook
    'Store the reference of the worksheet Company in the
    'variable mySheet01
    Set mySheet01 = myWk01.Worksheets("Company")
    'Determines the total number of rows in the list of mySheet01
    LastRow01 = mySheet01.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet01
    LastCol01 = mySheet01.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Open the workbook Company02
    Workbooks.Open myWk01path & "Company02"
 
    'Store the reference of the helper workbook in the
    'variable myWk02
    Set myWk02 = ActiveWorkbook
    'Store the reference of the worksheet CompanyAux in the
    'variable mySheet02
    Set mySheet02 = myWk02.Worksheets("CompanyAux")
    'Determines the total number of rows in the list of mySheet02
    LastRow02 = mySheet02.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet02
    LastCol02 = mySheet02.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Activeted the master workbook
    myWk01.Activate
 
    'Copy the labels (Company Name, Format and Dashboard) to 2
    'columns to the right of the list of the worksheet mySheet01
    'for helper the AdvancedFilter
    mySheet01.Range(Cells(1, 4).Address, Cells(1, 6).Address).Copy _
        Destination:=mySheet01.Cells(1, LastCol01 + 2)
 
    'Filters the data of the list os the worksheet CompanyAux using
    'as a criterion the column Company Name of the worksheet Company.
    'And copies the result to two columns to the right of
    'the list of the worksheet Company.
    mySheet02.Range(Cells(1, 1).Address, Cells(LastRow02, 3).Address).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=mySheet01.Range(Cells(1, LastCol01 + 2), Cells(1, LastCol01 + 4)), _
        CriteriaRange:=mySheet01.Range(Cells(1, 4), Cells(LastRow01, 4)), _
        Unique:=True
    'Save and Close the workbook Helper (Company02)
    myWk02.Close SaveChanges:=True
 
    'Create two formulas with the function Vlookup to fill the
    'colunms Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 5)).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],R2C9:R6C11,2,FALSE)"
    Range(Cells(2, 6), Cells(LastRow01, 6)).FormulaR1C1 = _
        "=VLOOKUP(RC[-2],R2C9:R6C11,3,FALSE)"
 
    'Transforms the formulas to values in the colunms
    'Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 6)).Value = _
        Range(Cells(2, 5), Cells(LastRow01, 6)).Value
 
    'Clear the range create by the AdvancedFilter
    '(range that no more necessary)
    Cells(1, LastCol01 + 2).CurrentRegion.Clear
 
    'Fill the column Front Desk
    For i = 2 To LastRow01
        Set myCell = Cells(i, 5)
        Select Case myCell.Value
            Case Is = "first.last"
                Cells(i, 7).Value = Cells(i, 2).Value & "." & _
                    Cells(i, 3).Value
            Case Is = "first-last"
                Cells(i, 7).Value = Cells(i, 2).Value & "-" & _
                    Cells(i, 3).Value
            Case Is = "firstlast"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Cells(i, 3).Value
            Case Is = "flast"
                Cells(i, 7).Value = Left(Cells(i, 2).Value, 1) & _
                    Cells(i, 3).Value
            Case Is = "firstl"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Left(Cells(i, 3).Value, 1)
        End Select
 
        Cells(i, 7).Value = Cells(i, 7).Value & "-ast"
    Next i
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
    Exit Sub
 
'Beginning of the error routine
ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & _
        ". Error description=" & Err.Description & "."
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
 
End Sub

After that, click the right mouse button on the sheet tab master (Company) of the master (Company01) and choose the view code command.

In Excel VBA window, open the Insert menu, choose the command Module.
Now, paste the previously copied code in VBA Excel window.

Close the VBA window of Excel and save the workbook master (Company01).

Now, with the active master worksheet, turn on the Viewtab, choose the command Macros in the Macro group.

In the Macro dialog box, select the VlookupComb macro and choose the Run command.

Markmzz
 
Upvote 0
markmzz said:
Or do the following (for two workbooks):

Note: I used the names Company01 and Company to the workbook and worksheet masters respectively. Additionally, I used the names Company02 and CompanyAux to the workbook and worksheet helpers respectively.

Workbook - Company01
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">First Name</TD><TD style="FONT-WEIGHT: bold">Last Name</TD><TD style="FONT-WEIGHT: bold">Company Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Format</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dashboard</TD><TD style="FONT-WEIGHT: bold">Front Desk</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Axe Juliet</TD><TD>Axe</TD><TD>Juliet</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>Axe.Juliet-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Rob Thomson</TD><TD>Rob</TD><TD>Thomson</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>Rob-Thomson-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Christiana Roberts</TD><TD>Christiana</TD><TD>Roberts</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>ChristianaRoberts-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Julie Valentine</TD><TD>Julie</TD><TD>Valentine</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>JValentine-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Alec Mac</TD><TD>Alec</TD><TD>Mac</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>AlecM-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>First01 Last01</TD><TD>First01</TD><TD>Last01</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First01Last01-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>First02 Last02</TD><TD>First02</TD><TD>Last02</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>First02L-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>First03 Last03</TD><TD>First03</TD><TD>Last03</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD><TD>First03L-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>First04 Last04</TD><TD>First04</TD><TD>Last04</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>First04-Last04-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>First05 Last05</TD><TD>First05</TD><TD>Last05</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First05Last05-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>First06 Last06</TD><TD>First06</TD><TD>Last06</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>FLast06-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>First07 Last07</TD><TD>First07</TD><TD>Last07</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD><TD>First07-Last07-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>First08 Last08</TD><TD>First08</TD><TD>Last08</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD><TD>FLast08-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>First09 Last09</TD><TD>First09</TD><TD>Last09</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>First09.Last09-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>First10 Last10</TD><TD>First10</TD><TD>Last10</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD><TD>First10.Last10-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>First11 Last11</TD><TD>First11</TD><TD>Last11</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First11Last11-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>First12 Last12</TD><TD>First12</TD><TD>Last12</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First12Last12-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>First13 Last13</TD><TD>First13</TD><TD>Last13</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First13Last13-ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>First14 Last14</TD><TD>First14</TD><TD>Last14</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD><TD>First14Last14-ast</TD></TR></TBODY></TABLE>Sheet - Company



Workbook - Company02
<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Company Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Format</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dashboard</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Astound</TD><TD style="TEXT-ALIGN: center">first.last</TD><TD style="TEXT-ALIGN: center">ast</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Albia</TD><TD style="TEXT-ALIGN: center">first-last</TD><TD style="TEXT-ALIGN: center">alb</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Apple</TD><TD style="TEXT-ALIGN: center">firstlast</TD><TD style="TEXT-ALIGN: center">bos</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Example</TD><TD style="TEXT-ALIGN: center">flast</TD><TD style="TEXT-ALIGN: center">egx</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Boston</TD><TD style="TEXT-ALIGN: center">firstl</TD><TD style="TEXT-ALIGN: center">apl</TD></TR></TBODY></TABLE>
Sheet - CompanyAux



Important: do all tests initially using the same layouts of workbooks Company01 and Company02.

Copy the code below.

Code: 
Sub [COLOR=blue]VlookupComb[/COLOR]()
'
'Prg    : VlookupComb
'Author : Markmzz
'Date   : 19/06/2011
'Version: 01
 
    'Define the macros variables
    Dim myWk01, myWk02 As Workbook
    Dim mySheet01, mySheet02 As Worksheet
    Dim myWk01path As String
    Dim LastCol01, LastRow01 As Long
    Dim LastCol02, LastRow02, i As Long
    Dim myCell As Range
 
    'Initializes the error routine
    On Error GoTo ErrorHandler
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = False
 
    'Store the reference of path the of the master
    'workbook (Company01) in the variable myWk01path
    myWk01path = ThisWorkbook.Path & "\"
    'Store the reference of the master workbook in the
    'variable myWk01
    Set myWk01 = ActiveWorkbook
    'Store the reference of the worksheet Company in the
    'variable mySheet01
    Set mySheet01 = myWk01.Worksheets("Company")
    'Determines the total number of rows in the list of mySheet01
    LastRow01 = mySheet01.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet01
    LastCol01 = mySheet01.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Open the workbook Company02
    Workbooks.Open myWk01path & "Company02"
 
    'Store the reference of the helper workbook in the
    'variable myWk02
    Set myWk02 = ActiveWorkbook
    'Store the reference of the worksheet CompanyAux in the
    'variable mySheet02
    Set mySheet02 = myWk02.Worksheets("CompanyAux")
    'Determines the total number of rows in the list of mySheet02
    LastRow02 = mySheet02.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet02
    LastCol02 = mySheet02.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Activeted the master workbook
    myWk01.Activate
 
    'Copy the labels (Company Name, Format and Dashboard) to 2
    'columns to the right of the list of the worksheet mySheet01
    'for helper the AdvancedFilter
    mySheet01.Range(Cells(1, 4).Address, Cells(1, 6).Address).Copy _
        Destination:=mySheet01.Cells(1, LastCol01 + 2)
 
    'Filters the data of the list os the worksheet CompanyAux using
    'as a criterion the column Company Name of the worksheet Company.
    'And copies the result to two columns to the right of
    'the list of the worksheet Company.
    mySheet02.Range(Cells(1, 1).Address, Cells(LastRow02, 3).Address).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=mySheet01.Range(Cells(1, LastCol01 + 2), Cells(1, LastCol01 + 4)), _
        CriteriaRange:=mySheet01.Range(Cells(1, 4), Cells(LastRow01, 4)), _
        Unique:=True
    'Save and Close the workbook Helper (Company02)
    myWk02.Close SaveChanges:=True
 
    'Create two formulas with the function Vlookup to fill the
    'colunms Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 5)).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],R2C9:R6C11,2,FALSE)"
    Range(Cells(2, 6), Cells(LastRow01, 6)).FormulaR1C1 = _
        "=VLOOKUP(RC[-2],R2C9:R6C11,3,FALSE)"
 
    'Transforms the formulas to values in the colunms
    'Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 6)).Value = _
        Range(Cells(2, 5), Cells(LastRow01, 6)).Value
 
    'Clear the range create by the AdvancedFilter
    '(range that no more necessary)
    Cells(1, LastCol01 + 2).CurrentRegion.Clear
 
    'Fill the column Front Desk
    For i = 2 To LastRow01
        Set myCell = Cells(i, 5)
        Select Case myCell.Value
            Case Is = "first.last"
                Cells(i, 7).Value = Cells(i, 2).Value & "." & _
                    Cells(i, 3).Value
            Case Is = "first-last"
                Cells(i, 7).Value = Cells(i, 2).Value & "-" & _
                    Cells(i, 3).Value
            Case Is = "firstlast"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Cells(i, 3).Value
            Case Is = "flast"
                Cells(i, 7).Value = Left(Cells(i, 2).Value, 1) & _
                    Cells(i, 3).Value
            Case Is = "firstl"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Left(Cells(i, 3).Value, 1)
        End Select
 
        Cells(i, 7).Value = Cells(i, 7).Value & "-ast"
    Next i
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
    Exit Sub
 
'Beginning of the error routine
ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & _
        ". Error description=" & Err.Description & "."
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
 
End Sub

After that, click the right mouse button on the sheet tab master (Company) of the master (Company01) and choose the view code command.

In Excel VBA window, open the Insert menu, choose the command Module.
Now, paste the previously copied code in VBA Excel window.

Close the VBA window of Excel and save the workbook master (Company01).

Now, with the active master worksheet, turn on the Viewtab, choose the command Macros in the Macro group.

In the Macro dialog box, select the VlookupComb macro and choose the Run command.

Markmzz
Click to expand...

Hi Mark,

Thanks for all the help. There seems to be one thing :
In the workbook Company01-
Column G "Front Desk", all the values are coming with suffix "-ast" which should not be this way.
The suffix of each value of column G depends on column F, and each value of column F depends on column D.

Eg:
If Company Name D2 is Astound, then its Format E2 is First.last and its suffix/Dashboard F2 is ast and the value in G2 should come:
B2.C2-F2

But if the company name changes in D, the value of E "Format" can change or remain same, but the values in F "Dashboard" will change as per the company name.

The Company Name, Format and Dashboard will be defined in the workbook Company02.

Thanks.
 
Last edited:
Upvote 0
Try this:

Code: 
Sub VlookupComb()
'
'Prg    : VlookupComb
'Author : Markmzz
'Date   : 21/06/2011
'Version: 02
 
    'Define the macros variables
    Dim myWk01, myWk02 As Workbook
    Dim mySheet01, mySheet02 As Worksheet
    Dim myWk01path As String
    Dim LastCol01, LastRow01 As Long
    Dim LastCol02, LastRow02, i As Long
    Dim myCell As Range
 
    'Initializes the error routine
    On Error GoTo ErrorHandler
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = False
 
    'Store the reference of path the of the master
    'workbook (Company01) in the variable myWk01path
    myWk01path = ThisWorkbook.Path & "\"
    'Store the reference of the master workbook in the
    'variable myWk01
    Set myWk01 = ActiveWorkbook
    'Store the reference of the worksheet Company in the
    'variable mySheet01
    Set mySheet01 = myWk01.Worksheets("Company")
    'Determines the total number of rows in the list of mySheet01
    LastRow01 = mySheet01.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet01
    LastCol01 = mySheet01.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Open the workbook Company02
    Workbooks.Open myWk01path & "Company02"
 
    'Store the reference of the helper workbook in the
    'variable myWk02
    Set myWk02 = ActiveWorkbook
    'Store the reference of the worksheet CompanyAux in the
    'variable mySheet02
    Set mySheet02 = myWk02.Worksheets("CompanyAux")
    'Determines the total number of rows in the list of mySheet02
    LastRow02 = mySheet02.Cells(Rows.Count, 1).End(xlUp).Row
    'Determines the total number of columns in the list of mySheet02
    LastCol02 = mySheet02.Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Activeted the master workbook
    myWk01.Activate
 
    'Copy the labels (Company Name, Format and Dashboard) to 2
    'columns to the right of the list of the worksheet mySheet01
    'for helper the AdvancedFilter
    mySheet01.Range(Cells(1, 4).Address, Cells(1, 6).Address).Copy _
        Destination:=mySheet01.Cells(1, LastCol01 + 2)
 
    'Filters the data of the list os the worksheet CompanyAux using
    'as a criterion the column Company Name of the worksheet Company.
    'And copies the result to two columns to the right of
    'the list of the worksheet Company.
    mySheet02.Range(Cells(1, 1).Address, Cells(LastRow02, 3).Address).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=mySheet01.Range(Cells(1, LastCol01 + 2), Cells(1, LastCol01 + 4)), _
        CriteriaRange:=mySheet01.Range(Cells(1, 4), Cells(LastRow01, 4)), _
        Unique:=True
    'Save and Close the workbook Helper (Company02)
    myWk02.Close SaveChanges:=True
 
    'Create two formulas with the function Vlookup to fill the
    'colunms Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 5)).FormulaR1C1 = _
        "=VLOOKUP(RC[-1],R2C9:R6C11,2,FALSE)"
    Range(Cells(2, 6), Cells(LastRow01, 6)).FormulaR1C1 = _
        "=VLOOKUP(RC[-2],R2C9:R6C11,3,FALSE)"
 
    'Transforms the formulas to values in the colunms
    'Format and Dashboard
    Range(Cells(2, 5), Cells(LastRow01, 6)).Value = _
        Range(Cells(2, 5), Cells(LastRow01, 6)).Value
 
    'Clear the range create by the AdvancedFilter
    '(range that no more necessary)
    Cells(1, LastCol01 + 2).CurrentRegion.Clear
 
    'Fill the column Front Desk
    For i = 2 To LastRow01
        Set myCell = Cells(i, 5)
        Select Case myCell.Value
            Case Is = "first.last"
                Cells(i, 7).Value = Cells(i, 2).Value & "." & _
                    Cells(i, 3).Value
            Case Is = "first-last"
                Cells(i, 7).Value = Cells(i, 2).Value & "-" & _
                    Cells(i, 3).Value
            Case Is = "firstlast"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Cells(i, 3).Value
            Case Is = "flast"
                Cells(i, 7).Value = Left(Cells(i, 2).Value, 1) & _
                    Cells(i, 3).Value
            Case Is = "firstl"
                Cells(i, 7).Value = Cells(i, 2).Value & _
                    Left(Cells(i, 3).Value, 1)
        End Select
 
       [COLOR=blue][B]Cells(i, 7).Value = Cells(i, 7).Value & "-" & Cells(i, 6).Value[/B][/COLOR]
    Next i
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
    Exit Sub
 
'Beginning of the error routine
ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & _
        ". Error description=" & Err.Description & "."
 
    'Enable the ScreenUpdating
    Application.ScreenUpdating = True
 
    'Frees the memory used by reference variables
    Set myCell = Nothing
    Set myWk01 = Nothing
    Set mySheet01 = Nothing
    Set myWk02 = Nothing
    Set mySheet02 = Nothing
 
End Sub

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
V/Hlookup or Index/Match assistance Required?
Replies
7
Views
101
etaf
etaf
N
  • Solved
Help with a VBA I’m working on
Replies
8
Views
184
Zot
Z
D
  • Question Question
need to pull info from 2 different spreadsheets
Replies
2
Views
87
alansidman
A
A
  • Solved
count how many times text string appears in column
Replies
2
Views
222
ajm
A
L
  • Solved
Index and Match
Replies
5
Views
383
Lizzi
L

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top