B
Buddy
Sub ColumnMatch()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
Dim nr3 As Long
Application.ScreenUpdating = False
Set ws1 = Sheets("R1")
Set ws2 = Sheets("R1")
Set ws3 = Sheets("R1")
Set LookInR = ws1.Range("A1").CurrentRegion
Set LookForR = Range(ws2.Range("A2"), ws2.Range("A" &
Rows.Count).End(xlUp))
nr3 = ws3.Range("A" & Rows.Count).End(xlUp).Row + 1
For Each c In LookForR
With LookInR
Set FoundOne = .Find(What:=c, LookAt:=xlPart)
Do While Not FoundOne Is Nothing
FoundOne.EntireRow.Cut Destination:=ws3.Cells(nr3, 1)
nr3 = nr3 + 1
Set FoundOne = .FindNext
Loop
End With
Next c
Set ws1 = Nothing
Set ws2 = Nothing
Set ws3 = Nothing
Set LookInR = Nothing: Set LookForR = Nothing
Application.ScreenUpdating = True
End Sub
I am trying to adapt this code to perform the following functions but it’s
not going that well, any feedback so that I could get it to perform the steps
below would be helpful.
1. Go to Sheet “R1†look at the contents in cell A1 then look for a
duplicate of those contents in Column C and Column E.
2. When Column A has duplicates in Column C and Column E, copy that row of
Column A and include Column B, then copy the matching row in Column C while
including Column D, and finally copy the matching row of column Column E
while including the same row of Column F and Column G. In other words A:B
belong together, C
belong together, and E:G belong together, but I want to
group these Columns together based on the contents in Columns A, C, and E.
3. Create a new worksheet and name it “Finalâ€
4. Select worksheet “Final†and paste Columns A:B, Columns C, and Columns
E:G from sheet “R1â€, which may have all been in all different rows, into the
same row in sheet “Finalâ€.
Go back to sheet “R1â€and repeat the same process for every row in Column A.
While including these two conditions
1. If Column A in sheet “R1†does not have a match in Column C and Column E
then leave it alone.
2. If Column A sheet “R1†has more than one match in Column C and Column E,
copy only the rows in Columns C, and Columns E:G where the duplicates
exist. Select sheet “Final†and underneath the 1st time the matching
contents were pasted in C and E:G paste the duplicates. After all the
duplicates have been pasted protect the same rows in Columns A:B so that no
more data can be pasted into them.
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim FoundOne As Range, LookInR As Range, LookForR As Range, c As Range
Dim nr3 As Long
Application.ScreenUpdating = False
Set ws1 = Sheets("R1")
Set ws2 = Sheets("R1")
Set ws3 = Sheets("R1")
Set LookInR = ws1.Range("A1").CurrentRegion
Set LookForR = Range(ws2.Range("A2"), ws2.Range("A" &
Rows.Count).End(xlUp))
nr3 = ws3.Range("A" & Rows.Count).End(xlUp).Row + 1
For Each c In LookForR
With LookInR
Set FoundOne = .Find(What:=c, LookAt:=xlPart)
Do While Not FoundOne Is Nothing
FoundOne.EntireRow.Cut Destination:=ws3.Cells(nr3, 1)
nr3 = nr3 + 1
Set FoundOne = .FindNext
Loop
End With
Next c
Set ws1 = Nothing
Set ws2 = Nothing
Set ws3 = Nothing
Set LookInR = Nothing: Set LookForR = Nothing
Application.ScreenUpdating = True
End Sub
I am trying to adapt this code to perform the following functions but it’s
not going that well, any feedback so that I could get it to perform the steps
below would be helpful.
1. Go to Sheet “R1†look at the contents in cell A1 then look for a
duplicate of those contents in Column C and Column E.
2. When Column A has duplicates in Column C and Column E, copy that row of
Column A and include Column B, then copy the matching row in Column C while
including Column D, and finally copy the matching row of column Column E
while including the same row of Column F and Column G. In other words A:B
belong together, C
belong together, and E:G belong together, but I want togroup these Columns together based on the contents in Columns A, C, and E.
3. Create a new worksheet and name it “Finalâ€
4. Select worksheet “Final†and paste Columns A:B, Columns C
, and ColumnsE:G from sheet “R1â€, which may have all been in all different rows, into the
same row in sheet “Finalâ€.
Go back to sheet “R1â€and repeat the same process for every row in Column A.
While including these two conditions
1. If Column A in sheet “R1†does not have a match in Column C and Column E
then leave it alone.
2. If Column A sheet “R1†has more than one match in Column C and Column E,
copy only the rows in Columns C
, and Columns E:G where the duplicatesexist. Select sheet “Final†and underneath the 1st time the matching
contents were pasted in C
and E:G paste the duplicates. After all theduplicates have been pasted protect the same rows in Columns A:B so that no
more data can be pasted into them.