SubSun

asked on

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

A sample workbook would be a great help here.

ASKER

Here you go.. Expected result in the result sheet..

Sample.xlsx

Sample.xlsx

ASKER CERTIFIED SOLUTION

membership

This content is only available to members.

To access this content, you must be a member of Experts Exchange.

ASKER

Thanks, It's working as expected..

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
```

ASKER

Thanks!.. I appreciate it..

Yw...Happy to help.. :-)

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
```

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.

=VLOOKUP(Value,Range,Offse

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.