Matching Columns
 

Hi All,

I have 6 Column with multiple rows data in excel sheet. The example is given
below:

A B C D E F
x 1 x x x x
x x x x x x
x x x 1 x 2

I want to compare Column B value with Column D value. If this match, then
copy the value given in Column F into column G and in Column H show the Row
Number from which the value is copied from.

Results needed:

A B C D E F G H
x 1 x x x x 2 R3
x x x x x x x x
x x x 1 x 2 x x

Thanks in advance.

Matching Columns
 

Try this. I assumed row 1 has headers and your data starts in row 2.
Change this as needed. HTH Otto
Sub DoIt()
Dim rColB As Range, rColD As Range
Dim i As Range, FoundCell As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
For Each i In rColB
If Not rColD.Find(What:=i, LookAt:=xlWhole) Is Nothing Then
Set FoundCell = rColD.Find(What:=i, LookAt:=xlWhole)
i.Offset(, 5) = FoundCell.Offset(, 2)
i.Offset(, 6) = "R" & FoundCell.Row
End If
Next i
End Sub

"ng6971" wrote in message
...
Hi All,

I have 6 Column with multiple rows data in excel sheet. The example is
given
below:

A B C D E F
x 1 x x x x
x x x x x x
x x x 1 x 2

I want to compare Column B value with Column D value. If this match, then
copy the value given in Column F into column G and in Column H show the
Row
Number from which the value is copied from.

Results needed:

A B C D E F G H
x 1 x x x x 2 R3
x x x x x x x x
x x x 1 x 2 x x

Thanks in advance.

Matching Columns
 

Thank you very much. Works perfect.



"Otto Moehrbach" wrote:

Try this. I assumed row 1 has headers and your data starts in row 2.
Change this as needed. HTH Otto
Sub DoIt()
Dim rColB As Range, rColD As Range
Dim i As Range, FoundCell As Range
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
Set rColD = Range("D2", Range("D" & Rows.Count).End(xlUp))
For Each i In rColB
If Not rColD.Find(What:=i, LookAt:=xlWhole) Is Nothing Then
Set FoundCell = rColD.Find(What:=i, LookAt:=xlWhole)
i.Offset(, 5) = FoundCell.Offset(, 2)
i.Offset(, 6) = "R" & FoundCell.Row
End If
Next i
End Sub

"ng6971" wrote in message
...
Hi All,

I have 6 Column with multiple rows data in excel sheet. The example is
given
below:

A B C D E F
x 1 x x x x
x x x x x x
x x x 1 x 2

I want to compare Column B value with Column D value. If this match, then
copy the value given in Column F into column G and in Column H show the
Row
Number from which the value is copied from.

Results needed:

A B C D E F G H
x 1 x x x x 2 R3
x x x x x x x x
x x x 1 x 2 x x

Thanks in advance.

.