VBA to look up information from a different sheet where data is placed in random columns.
I have a master template which is save account information and when some information are missing I refer to another s/s which has the missing information. The master file is attached with name - acct info and the secondary s/s is attached with name - specific info.
What I am trying to do is vlookup to get the missing information from the secondary s/s. But there are lot of issues to use vlookup manually for many reasons. The file is too large. with at least thousands of lines. and the formula breaks if a particular row has the information and is not blank. The columns in both sheets are not in the same order so can't do vlook up to multiple columns at the same time. We keep receiving the update secondary file, once in two days its a tedious task to do lookup manually.
So I tried doing the record macro, and record macro is same as doing it manually. The formula stops when there is data in a particular row and have to do it for all the columns that needs to be bought into the master file.
So I need a VBA code to look up and bring the values from secondary s/s to the master file.
The master file has 5 account holder information sections (I have highlighted every acct holder section in different colors). The client specific information are all in the secondary file. The secondary file all information of all account holders. For eg : Ross could be account holder 1 and rachael could be acct holder 2. In the secondary file, we wouldn't know if Ross is account holder 1 or 2. But in the master file, their names would be updated under respective columns. In this scenario, Ross would be updated under acct holder 1 name and Rachael would be updated under Acct holder 2 name. So I need code to look up for the names in master file and bring all correspondent info from the secondary file. Since the columns are not in the same order something to match the column headers would be good.
The code should first - look for names in Acct holder 1 column i.e, column CC and look for these names in specific info s/s and bring all other data. The column header also do not match in both the files. So something like
match the LEGAL NAME in specific info workbook to match 1ACCT HOLDER, if match is true then look up for other headers and bring over the data. I have listed few columns from both the workbook that should be looked up. If Ross is account holder 1 it should be matched in the below way
Acct Info/Master template Specific Info/Secondary file
1SSN SOCIAL SECURITY NUMBER
1DOB DATE OF BIRTH
1EMAIL ADDRESS EMAIL ADDRESS
1PH NUMBER CELL CELL PHONE NUMBER
Similarly, the code should then look up information for account holder 2 i.e column DY in the master file. The same should be done for Acct holder 3, 4 and 5
Since the client information is received in multiple batches, is there a way to highlight or move the only the rows that was updated to another sheet? I am looking for some way to keep track of information that was updated so we don't duplicate the accts. For eg, if 5 acct information was updated in master file using the secondary file, we initiate those acct to be opened. When the secondary file is updated with more client information lets say 5 more was added, we do not open all the 10 accts now. Because the first when I ran the code I had already initiated the first 5 accts to be opened.
I am sorry for the long description. Just wanted to be as clear as possible.
Thank you very much in advance.
ASKER
ACCT-INFO.xlsm
ASKER
sorry for the belated follow up!
Sub UpdateAcctInfo()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rowFound As Range
Dim nameToFind As String
Dim i As Long, j As Long
Dim lastRow As Long
Set wb1 = ThisWorkbook ' master template
Set ws1 = wb1.Worksheets("Sheet1") ' master sheet
lastRow = ws1.Cells(ws1.Rows.Count, 3).End(xlUp).Row ' get last row of data in master sheet
Set wb2 = Workbooks.Open("path\to\specific\info\file.xlsx") ' secondary sheet
Set ws2 = wb2.Worksheets("Sheet1") ' secondary sheet
' Loop through account holders 1 to 5
For i = 1 To 5
' Get the legal name of the account holder from the master sheet
nameToFind = ws1.Cells(2, 82 + (i - 1) * 18).Value ' assuming legal name is in row 2, column 82+i*18
' Find the row in the secondary sheet that corresponds to the legal name
Set rowFound = ws2.Columns("A").Find(what:=nameToFind, LookIn:=xlValues, lookat:=xlWhole)
If Not rowFound Is Nothing Then
' Extract the required information from the corresponding columns
For j = 1 To 4
ws1.Cells(lastRow + 1, 84 + (i - 1) * 18 + j - 1).Value = _
ws2.Cells(rowFound.Row, j + 1).Value
Next j
End If
Next i
' Close the secondary sheet without saving changes
wb2.Close SaveChanges:=False
End SubCheers
ASKER
There are 5 account holders listed under 1Acct holder column. And the first one is Rachael. When the run the macro, all data related to Rachael is being copied under all 5 acct holders. It is not actually looking up for the name listed in the Acct holder column and looking up for correspondent information from specific info workbook. In the main sheet, I have also listed 2acct holders - Ross and Rachael. Ross the 2nd acct holder in row 2. But Rachael's information is copied against Ross.
Another problem I see that, the data is not getting copied in the right columns. I updated the right col ref for SSN, DOB, Email, and cell phone from both the sheets but yet the data is copied under wrong columns. DOB is coming under SSN, SSN is coming under DOB, email is coming under phone number etc.
Last problem is that its not moving onto the next account holder. It is copying only the first account holder info into all the 5acct holders in master file.
Updated code is in the attached file. I have run the macro, so you can see where the data is copied into in this file.
Also what kind of code can I add to move the updated rows into another sheet.
Thank you in advance.