Compare two Workbook to display full name column

FlaShow

New Member
Joined
Nov 23, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello Friends,

I was able before to Copy Column base on Condition from "absent11d7.xlsx" file to display the result in "NewAbsentData.xlsx" using the Code below.
Currently i want to move One Step Above: The final file "NewAbsentData.xlsx" have two columns "Last name" & "First name" So
As Optional Step using Checkbox would like to Compare "Email address" column in "NewAbsentData.xlsx" file with "cid" in "students.xlsx" file
To Display "Full name" column instead of "Last name" & "First name" in "NewAbsentData.xlsx" file. How i can do that ?

Note: "Email address" column contain cid so if email is: S304101600358@moe.edu.kw than the cid will be 304101600358.

Files to test:

VBA Code:
Sub CopyCols()
    Application.ScreenUpdating = False
    Dim wsCopy As Worksheet, WB As Workbook, x As Long, LastRow As Long, lCol As Long
    Set WB = ThisWorkbook
    Workbooks.Open "C:\Users\Working\Desktop\absent11d7.xlsx"
    Set wsCopy = Sheets(1)
    wsCopy.Cells.Replace What:="", Replacement:="|", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    wsCopy.Cells.Replace What:="|", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Workbooks.Add 1
    With wsCopy
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For x = 1 To lCol
            If WorksheetFunction.CountA(.Columns(x)) >= LastRow - 11 Then
                .Columns(x).Copy Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
            End If
        Next x
    End With
    Columns(1).Delete
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=WB.Path & "\NewAbsentData.xlsx"
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

FlaShow

New Member
Joined
Nov 23, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Sorry to b annoying But Really Need guidance Here
 

FlaShow

New Member
Joined
Nov 23, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Still Looking For A Hint To Help With This
 

FlaShow

New Member
Joined
Nov 23, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Sorry And For Last Time Still Looking for a Hint To Help Me With This
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top