• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro For Concatenate and Vlook up against Concatenation

Hi,

I uploaded 2 excel sheet with file name Master A and Master B spreadsheet (workbook).

I want a Macro on Master B sheet that will concantenate Column A to C and the result will display in column D.

Against that concantenation result of column D i want to vlook up that column D into Master A workbook column A with the result of Master A column B will displayed in workbook Master B column E.

Basically I want a macro logic that will concantenate 3 or more column and against that concantenation it will vlook up other file since that is the only common thing to both file but other file has need to do concantenation so that the vlookup will be successful.

Please find attached sample file.
 

Attachments

  • MASTER-A.xlsx
    8 KB · Views: 6
  • MASTER-B.xlsx
    8.1 KB · Views: 6
vba is not necessary for this.

In MasterB cell D2 type =A2&B2&C2
In MasterB cell E2 type =VLOOKUP(D2,'[MASTER-A.xlsx]Sheet1'!$A$2:$B$3,2,0)

Then Copy both down.
 
I need a VBA macro.. This is just a basic.. because it will come to huge data after this, thats why i just give simple sample for easy understanding.

Actually after this it has to save and create new template (workbook).
I'll just starting in basic one first.

vba is not necessary for this.

In MasterB cell D2 type =A2&B2&C2
In MasterB cell E2 type =VLOOKUP(D2,'[MASTER-A.xlsx]Sheet1'!$A$2:$B$3,2,0)

Then Copy both down.
 
okey dokey.

Code:
Option Explicit

Sub foo()
    Dim wbThis As Workbook
    Dim wbSource As Workbook
    Dim strName As String
    Dim thePath As String
    Dim lrThis As Long
    Dim lrSource As Long
    Dim res As Variant, i As Long

    Application.ScreenUpdating = False
    Set wbThis = ActiveWorkbook
    strName = "MASTER-A"

    thePath = "C:\Users\Alan\Desktop\"    'Change this path to your path
    Set wbSource = Workbooks.Open(thePath & strName & ".xlsx")
    wbSource.Activate
    lrSource = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    wbThis.Activate
    lrThis = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("D2:D" & lrThis).FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("D" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 2, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("E" & i) = res
        End If
    Next i
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    wbThis.Save
    wbSource.Close
    wbThis.Activate
    MsgBox "Completed Action"
End Sub
 
Thank You...

Its doing concatenate...But Vlookup to other file is not happening.


okey dokey.

Code:
Option Explicit

Sub foo()
    Dim wbThis As Workbook
    Dim wbSource As Workbook
    Dim strName As String
    Dim thePath As String
    Dim lrThis As Long
    Dim lrSource As Long
    Dim res As Variant, i As Long

    Application.ScreenUpdating = False
    Set wbThis = ActiveWorkbook
    strName = "MASTER-A"

    thePath = "C:\Users\Alan\Desktop\"    'Change this path to your path
    Set wbSource = Workbooks.Open(thePath & strName & ".xlsx")
    wbSource.Activate
    lrSource = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    wbThis.Activate
    lrThis = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("D2:D" & lrThis).FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("D" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 2, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("E" & i) = res
        End If
    Next i
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    wbThis.Save
    wbSource.Close
    wbThis.Activate
    MsgBox "Completed Action"
End Sub
 
Thank You So Much

@ AlanSidman

It's working fine now..
But through this basic things can you do a macro that the result of Master B vlook up and concatenation can be open in new workbook. As im planning to build a new template using the 2 existing workbook.

Basically a New Workbook using this 2 existing workbook should come or save as a new Workbook.

I REALLY APPRECIATE YOU SO MUCH. :)
 
Code:
Sheets("Sheet1").Range("D2:D" & lrThis).FormulaR1C1 = "=CONCATENATE(RC[-3],&" / "&RC[-2],&" / "&RC[-1])"

I'm trying to add also in the concatenate the "/" symbol..but im getting error, how can the / symbol can be added in vba concatenate? Thank You.
 
I added the forward slash "/" symbold in Master A and I want to concatenate this into Master B with / symbol so that it can do vlook up perfectly.

Attached are the updated file with / on Master A and The Macro on Master B which are getting error when i trying to concatenate with "/" symbol.

Pls check
 

Attachments

  • MASTER-A.xlsx
    8 KB · Views: 1
  • MASTER-B.xlsm
    16.3 KB · Views: 1
Hi already solved the / symbol problem it has to be in double double """ symbol, now my only problem is from this two workbook (spreadsheet) it has to create another new workbook (template) from the output of vlook up of Master B.

Can someone create the VBA macro for creating new workbook (template) from existing workbook, or basically from my Master A and Master B a new workbook should arrive from Master B workbook.

Appreciate your help. Thank You so much.
 
Hi before I go to 3rd template which is the grand finale of all this macro.
I need 2 things.

1.)
I want to do a vlookup in concatenation in macro without adding column for concatenation. Pls see my attachment Master-B. xlsm on column F. I achieve the vlookup result manually without adding column to concat but instead putting the concatenate formula itself inside the vlookup. I want to achieve this using macro, so that a new column will no need to add for concat. Manually this is the formula for vlookup

=VLOOKUP((CONCATENATE(A2&"/"&B2&"/"&C2)),'[MASTER-A.xlsx]Sheet1'!$A:$B,2,0)

I want this to achieve in macro.

2.)
Second thing is i Attached also Master A and added more column which i highlighted gold accent for easy understanding, I want that to achieved using the my above request number (1) as this will go a huge data onward, means using the reference of concatenated column inside vlookup (without adding new column to concatenate) the macro should put a multiple one time vlookup result in Master B workbook where the macro stored keeping the Master A as the reference of all the data.

3.)
The Grand Finale. - The 3rd Sheet as I will Name Grand_Finale_Workbook
Basically this is the final result of the above 2 request. This is a design template basically that will populate automatically by macro using the data in Master B.

The idea is whatever achieved in Master B workbook should populate on New Workbook which is a fix design template which i highlighted in Pink in the Grand_Finale_Workbook, whatever highlighted that is a fix data template and based which need to be populated based on the condition i written in Grand_Finale_workbook.

and this Grand_Finale__Workbook when generating must be save in .csv format rather than .xlsx but can be open in excell sheet (the normal csv thing when opening in excel sheet) and it will come comma delimeter when open in notepad.

THANK YOU SO MUCH Chandoo and Mr AlanSidman.

This is a great Macro once done and will be usefull for other readers too.

I attached all the 3 workbook for easy understanding.
 

Attachments

  • Grand_Finale_Workbook.xlsx
    8.9 KB · Views: 6
  • MASTER-A.xlsx
    8.3 KB · Views: 7
  • MASTER-B.xlsm
    18.2 KB · Views: 5
@AlanSidman

Hi Sir.. is this possible:?

Hi before I go to 3rd template which is the grand finale of all this macro.
I need 2 things.

1.)
I want to do a vlookup in concatenation in macro without adding column for concatenation. Pls see my attachment Master-B. xlsm on column F. I achieve the vlookup result manually without adding column to concat but instead putting the concatenate formula itself inside the vlookup. I want to achieve this using macro, so that a new column will no need to add for concat. Manually this is the formula for vlookup

=VLOOKUP((CONCATENATE(A2&"/"&B2&"/"&C2)),'[MASTER-A.xlsx]Sheet1'!$A:$B,2,0)

I want this to achieve in macro.

2.)
Second thing is i Attached also Master A and added more column which i highlighted gold accent for easy understanding, I want that to achieved using the my above request number (1) as this will go a huge data onward, means using the reference of concatenated column inside vlookup (without adding new column to concatenate) the macro should put a multiple one time vlookup result in Master B workbook where the macro stored keeping the Master A as the reference of all the data.

3.)
The Grand Finale. - The 3rd Sheet as I will Name Grand_Finale_Workbook
Basically this is the final result of the above 2 request. This is a design template basically that will populate automatically by macro using the data in Master B.

The idea is whatever achieved in Master B workbook should populate on New Workbook which is a fix design template which i highlighted in Pink in the Grand_Finale_Workbook, whatever highlighted that is a fix data template and based which need to be populated based on the condition i written in Grand_Finale_workbook.

and this Grand_Finale__Workbook when generating must be save in .csv format rather than .xlsx but can be open in excell sheet (the normal csv thing when opening in excel sheet) and it will come comma delimeter when open in notepad.

THANK YOU SO MUCH Chandoo and Mr AlanSidman.

This is a great Macro once done and will be usefull for other readers too.

I attached all the 3 workbook for easy understanding.
 
Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

I do have a life that does not center around this forum. I will look at this when I have time. At present, that may be a day or so.
 
Take a look at this. If I understand your request, this should do what you want.
Code:
Option Explicit

Sub Reg()
    Dim wbThis As Workbook
    Dim wbSource As Workbook
    Dim wbFinal As Workbook
    Dim strName As String
    Dim strFinal As String
    Dim thePath As String
    Dim lrThis As Long
    Dim lrSource As Long
    Dim lrF As Long
    Dim res As Variant, i As Long

    Application.ScreenUpdating = False
    Set wbThis = ActiveWorkbook
    strName = "MASTER-A"
    strFinal = "Grand_Finale_Workbook"
    thePath = "C:\Users\reggie\Desktop\Macro for testing\"
   Set wbSource = Workbooks.Open(thePath & strName & ".xlsx")
   Set wbFinal = Workbooks.Open(thePath & strFinal & "xlsx")
    wbSource.Activate
    lrSource = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    wbThis.Activate
    lrThis = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Application.CutCopyMode = False
    'Sheets("Sheet1").Range("D2:D" & lrThis).FormulaR1C1 = "=CONCATENATE(RC[-3]&""/""&RC[-2]&""/""&RC[-1])"
   ' For i = 2 To lrThis
        'On Error Resume Next
       ' Err.Clear
       ' res = Application.WorksheetFunction.VLookup(Range("D" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 2, False)
        'If Err.Number = 0 Then
            'Sheets("Sheet1").Range("E" & i) = res
       ' End If
    'Next i
   
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("A" & i) & Range("B" & i) & Range("C" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 2, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("F" & i) = res
        End If
    Next i
   
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("A" & i) & Range("B" & i) & Range("C" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 3, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("G" & i) = res
        End If
    Next i
   
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("A" & i) & Range("B" & i) & Range("C" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 4, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("H" & i) = res
        End If
    Next i
   
    For i = 2 To lrThis
        On Error Resume Next
        Err.Clear
        res = Application.WorksheetFunction.VLookup(Range("A" & i) & Range("B" & i) & Range("C" & i), wbSource.Sheets("Sheet1").Range("A2:B" & lrSource), 5, False)
        If Err.Number = 0 Then
            Sheets("Sheet1").Range("I" & i) = res
        End If
    Next i
   
    For i = 2 To lrThis
        lrF = wbFinal.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
        Range("F" & i & ":I" & i).Copy wbFinal.Sheets("Sheet1").Range("B" & lrF + 1)
    Next i
   
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    wbThis.Save
    wbSource.Close
    wbThis.Activate
    MsgBox "Completed Action"
End Sub
 
Back
Top