Link to home
Get AccessLog in
Avatar of SubSun
SubSunFlag for India

asked on

Match name and copy that row from one sheet to another on a corresponding column

I want to lookup a list of names in Sheet one and if the name matches, I need to copy that Row to another sheet.
User generated imageUser generated imageI am trying with vlookup, but I am able to copy only one column (I am not that expert in vlookup).. Please let me know if there any formula or macro to do this.. Many Thanks in advance..
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

A sample workbook would be a great help here.
Avatar of SubSun

ASKER

Here you go.. Expected result in the result sheet..
Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of SubSun

ASKER

Thanks, It's working as expected..
Avatar of SubSun

ASKER

I need help with a small addition, is it possible to add the names which are not there in Sheet1 to another sheet or in the same result sheet? Let me know if I need to open a new question for this.. Thanks in advance..
You can use this code..it will move the values which are not their to the missing worksheet...

Sub movdata()
    Dim rng As Range, cell As Range, r As Range
    Dim lr As Long, lr1 As Long, ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
    Dim k As Long, z As Long, ws3 As Worksheet
    Set ws = Sheets("Sheet1")
    Set ws1 = Sheets("Sheet2")
    Set ws2 = Sheets("Result")
    Set ws3 = Sheets("Missing")
    
    ws2.Cells.Clear

    lr = ws1.Cells(Cells.Rows.Count, "A").End(xlUp).Row

    If lr = 1 Then
        MsgBox " Please enter value to search"
        Exit Sub
    End If

    Set r = ws1.Range("A1:A" & lr)

    lr1 = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A2:A" & lr1)

    For Each cell In rng
        If Application.WorksheetFunction.CountIf(r, cell.Value) = 1 Then

            If ws2.Cells(1, 1).Value = "" Then
                cell.EntireRow.Copy ws2.Range("a1")
            Else
                cell.EntireRow.Copy ws2.Range("A" & ws2.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1)
            End If

          Else
          
                 If ws3.Cells(1, 1).Value = "" Then
                cell.EntireRow.Copy ws3.Range("a1")
            Else
                cell.EntireRow.Copy ws3.Range("A" & ws3.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1)
            End If
          
          
        End If


    Next cell


End Sub

Open in new window

Avatar of SubSun

ASKER

Thanks!.. I appreciate it..
Yw...Happy to help.. :-)
Avatar of SubSun

ASKER

Ah Sorry.. What I mean is. If I am searching for following, names and if 'HHH' is not in Sheet1 then 'HHH' should be copied to Missing Sheet.. Right now it's copying the missing items from Sheet1..

ABCD
EFG
HHH

Open in new window

You could have still done it with VLOOKUP

=VLOOKUP(Value,Range,Offset,LookupType)

Value - The cell reference for the name you are looking for
Range - The range in which you are looking with the lookup value in the leftmost column, include all columns of the data, for your example A to D
Offset - The number of the column from which you want to return the value, in your range A to D column B is number 2, C = 3, D=4. Even if your range was say E to H, F would still be 2 as it is the second column of the range, G = 3 ie 3rd column.
LookupType - Set to FALSE as you need an exact match. TRUE will give a the closest but not greater than match.

So for your data in column A you have the name and then columns B to D with the VLOOKUP formulae with changes in the offset value as you move right across the columns.