Add columns and delete columns based on the another workbook in VBA

[mayaa_mmm]

I have workbook A with sheet1 contains like below header

ID	Class	Mark	Grade
XX	AA	BB	CC
XX	AA	BB	CC
XX	AA	BB	CC
XX	AA	BB	CC

In the another workbook B contains like below but

ID	Class	Mark	Attended
XX	AA	BB	DD
XX	AA	BB	AB
XX	AA	BB	AC
XX	AA	BB	CA

In workbook A should contains ID, Class , Mark
Grade column deleted and Attended got added.

Basically column should get added and delete as per workbook B

ID	Class	Mark	Attended
XX	AA	BB	DD
XX	AA	BB	AB
XX	AA	BB	AC
XX	AA	BB	CA

 

[alansidman]

With Power Query, Merge the two tables.

Mcode in PQ follows:

let
    Source = Table.NestedJoin(Sheet1, {"Column1"}, Sheet2, {"Column1"}, "Sheet2", JoinKind.LeftOuter),
    #"Expanded Sheet2" = Table.ExpandTableColumn(Source, "Sheet2", {"Column4"}, {"Column4.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Sheet2",{"Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Class", type text}, {"Mark", type text}, {"Attended", type text}})
in
    #"Changed Type"

 

[vmjan02]

1. Considering that file Workbook B is name as Attended and have all the records in sheet1
2. Also consider that the workbook B is not open
3. Code will ask to open workbook B and well do rest of the required task
4. Ensuring both Workbook A and B have the records in sheet 1 respectively

Sub text()
Workbooks("A.xlsx").Sheets("Sheet1").Activate
With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    Workbooks.Open (fullpath) '' workbook B is open
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

Workbooks("A.xlsx").Sheets("Sheet1").Activate

ActiveSheet.Range("E1").Value = "Attende"
With Worksheets("Sheet1")
    With .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=VLOOKUP(A2,[B.xlsx]Sheet1!$A:$D,4,0)" ''-- can change the formula here
        .Value = .Value
     End With
End With
ActiveSheet.Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
End Sub

 

[mayaa_mmm]

With Power Query, Merge the two tables.

Mcode in PQ follows:

let
    Source = Table.NestedJoin(Sheet1, {"Column1"}, Sheet2, {"Column1"}, "Sheet2", JoinKind.LeftOuter),
    #"Expanded Sheet2" = Table.ExpandTableColumn(Source, "Sheet2", {"Column4"}, {"Column4.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Sheet2",{"Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Class", type text}, {"Mark", type text}, {"Attended", type text}})
in
    #"Changed Type"

Sir
thank you for your update.
But I am new to Power Query.
Link which you provided shows database error for inserting the power query

 

[mayaa_mmm]

1. Considering that file Workbook B is name as Attended and have all the records in sheet1
2. Also consider that the workbook B is not open
3. Code will ask to open workbook B and well do rest of the required task
4. Ensuring both Workbook A and B have the records in sheet 1 respectively

Sub text()
Workbooks("A.xlsx").Sheets("Sheet1").Activate
With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
       
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    Workbooks.Open (fullpath) '' workbook B is open
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

Workbooks("A.xlsx").Sheets("Sheet1").Activate

ActiveSheet.Range("E1").Value = "Attende"
With Worksheets("Sheet1")
    With .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=VLOOKUP(A2,[B.xlsx]Sheet1!$A:$D,4,0)" ''-- can change the formula here
        .Value = .Value
     End With
End With
ActiveSheet.Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
End Sub

Thank you so much
It works awesome

 

[vmjan02]

Great

 

[alansidman]

Link which you provided shows database error for inserting the power query

Thanks for letting me know. Look at the new link in my signature.