Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching 2 columns | Excel Discussion (Misc queries) | |||
Matching Columns | Setting up and Configuration of Excel | |||
matching on columns | Excel Discussion (Misc queries) | |||
I need help matching columns | Excel Programming | |||
matching 2 columns | Excel Programming |