Auto populate based on matched data from other sheet

[IKHAN]

hi,

Was wondering if this could be achieved thru vba as have tried using formulas and vlookup thru matching criteria and it takes forever searching huge database(Have around 5000 rows)

Have 3 conditions to meet:

1. Need to match and copy column A and B from sheet (Contacts) and auto populate data in sheet (4. mobile)based on matched names in column D and E in (4.mobile) based on header file

2. After above line is executed and If any data is missing in any row in sheet (4.mobile) against matched name,Data will be entered or corrected manually, That info must fill in the master sheet (contact) against that matched name.

example : In Contacts sheet "deskphone" is missing for "stephen Bog"

3. Lastly if no matched name - Data entered in (4 . mobile) row manually must copy to master sheet (Contacts) end of list.

Have attached sample file

 

[Marc L]

Hi !

Differents ways but first problem is data logic :
why in Mobile worksheet column B is empty (no header)
as well column G of Contacts worksheet ?!
These columns could be deleted ?

Without these columns you can use an advanced filter :
see Introduction to Excel Advanced Filters
and Extract data using Advanced Filter and VBA …
​

 

[IKHAN]

Marc,

That's a sample sheet attached and have other conditional formulas running in Column B of mobile and Column G of contacts.

1.Was able to get first issue solved by running this macro below to auto-populate data in (mobile) from master (Contact) sheet.

2.Issue am having is :extracted info based on b15-C15 in( mobile) and manually type any missing info and pasting back in master (Contact) sheet and also if no first and last name match - Complete row to be pasted in (contact) sheet.

FYI - original (contact)sheet will be hidden and pwd protected,so other users can't access due to privacy.

So macro is reqd. for users to modify or add data from (Mobile) sheet

Using below code to get info from Main sheet(Contact)

Will really appreciate if help is provided on issue 2.

Thanks again

Sub CopyBasedonSheet1() 'macro to pullinfo from data into contacts

Dim i As Long
Dim j As Long
Sheet4LastRow = Worksheets("4. Mobile").Range("D" & Rows.Count).End(xlUp).Row
sheet7LastRow = Worksheets("Contacts").Range("A" & Rows.Count).End(xlUp).Row

    For j = 1 To Sheet4LastRow
        For i = 1 To sheet7LastRow
            If Worksheets("4. Mobile").Cells(j, 4).Value = Worksheets("Contacts").Cells(i, 1).Value _
                And Worksheets("4. Mobile").Cells(j, 5).Value = Worksheets("Contacts").Cells(i, 2).Value Then
                Worksheets("4. Mobile").Cells(j, 1).Value = Worksheets("Contacts").Cells(i, 3).Value
                Worksheets("4. Mobile").Cells(j, 3).Value = Worksheets("Contacts").Cells(i, 4).Value
                Worksheets("4. Mobile").Cells(j, 6).Value = Worksheets("Contacts").Cells(i, 5).Value
                Worksheets("4. Mobile").Cells(j, 7).Value = Worksheets("Contacts").Cells(i, 6).Value
                Worksheets("4. Mobile").Cells(j, 8).Value = Worksheets("Contacts").Cells(i, 8).Value

            Else
            End If
    Next i
Next j
End Sub

 

[Marc L]

As an advanced filter is very faster than using a double loop
on an active display ! …

For issue2 & 3 you can use the Worksheet_Change event …
​

 

[IKHAN]

Not very familiar with with advanced filter and how will worksheet_change event solve issue 2 and 3.

In above attached file, extracting data and copying missing data back to original file..

Thanks for suggestions...

 

[Marc L]

So forsake event, just use the same way of the code in your previous post
called by a button …
​