On Aug 11, 9:20*pm, Joel wrote:
there are *a million different ways to do comparisons like this. *Everybody
wants something a little dfifferent. *Pardon me if I didn't interprete you
request properly. *I think you want columns C - H on sheet 1 put on sheet 2
columns C - H. *What is confusing me is your previous request ask for the
data to be placed either on sheet 1 or sheet 3. *Now it is sheet 2. *If it is
wrong in only takes me 2 minutes to make the changes. *No big deal.
Sub Duplicates()
* *'
* *' NOTE: The macro assumes there is a header in the both worksheets
* *' * * * The macro starts at row 2 and sort data automatically
* *'
* *'find matching rows in sheet 2
* *With Sheets("Sheet2")
* * * RowCount = 2
* * * Do While .Range("A" & RowCount) < ""
* * * * *ID = Trim(.Range("A" & RowCount))
* * * * *'compare - look for ID in Sheet 1
* * * * *With Sheets("Sheet1")
* * * * * * Set c = .Columns("A").Find(what:=ID, _
* * * * * * * *LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* * * * * * If Not c Is Nothing Then
* * * * * * * *'copy sheet 1 to sheet 2
* * * * * * * *Set CopyRange = _
* * * * * * * * * .Range("C" & c.Row & ":H" & c.Row)
* * * * * * * *CopyRange.Copy Destination:=Sheets("Sheet2").Range("C" &
RowCount)
* * * * * * End If
* * * * *End With
* * * * *RowCount = RowCount + 1
* * * Loop
* *End With
* *ScreenUpdating = True
End Sub
"Ty" wrote:
On Aug 11, 4:19 pm, Joel wrote:
I can easily do any or all of three things below:
1) Highlight the dups on Sheet 2
2) Put duplicates on sheet 3
3) If column B on sheet 1 has more data than A add the dups to sheet 1
starting after the LastRow in either A or b. *Then sort on A. *I can leave
the rows without column A data at the beginning or end of Sheet 1.
"Ty" wrote:
On Aug 11, 11:54 am, Joel wrote:
the IDs are not eactly matching. *This is usually caused by extra spaces in
the strings or some of the letters are in uppercase. *Try these changes.. *I
added MatchCase = False and added TRIM in two locations. *The code is looking
for an exact match in ID which means it is checking the entire cell to match.
Sub Duplicates()
* *'
* *' NOTE: The macro assumes there is a header in the both worksheets
* *' * * * The macro starts at row 2 and sort data automatically
* *'
* *ScreenUpdating = False
* *'get first empty row of sheet1
* *With Sheets("Sheet1")
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * NewRow = LastRow + 1
* *End With
* *'find matching rows in sheet 2
* *With Sheets("Sheet2")
* * * RowCount = 2
* * * Do While .Range("A" & RowCount) < ""
* * * * *ID = trim(.Range("A" & RowCount))
* * * * *Employee = trim(.Range("B" & RowCount))
* * * * *'compare - look for ID in Sheet 1
* * * * *With Sheets("Sheet1")
* * * * * * Set c = .Columns("A").Find(what:=ID, _
* * * * * * * *LookIn:=xlValues, lookat:=xlWhole,MatchCase:=False)
* * * * * * If Not c Is Nothing Then
* * * * * * * *'add to end of sheet 1
* * * * * * * *.Range("A" & NewRow) = ID
* * * * * * * *.Range("B" & NewRow) = Employee
* * * * * * * *NewRow = NewRow + 1
* * * * * * End If
* * * * *End With
* * * * *RowCount = RowCount + 1
* * * Loop
* *End With
* *'sort and highlight data
* *RowCount = 2
* *With Sheets("Sheet1")
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * 'sort data by column A
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlascendiing
* * * Do While .Range("A" & RowCount) < ""
* * * * *Set FirstItem = .Range("A" & RowCount)
* * * * *Set SecondItem = .Range("A" & (RowCount + 1))
* * * * *If FirstItem.Value = SecondItem.Value Then
* * * * * * FirstItem.Interior.Color = RGB(255, 0, 0)
* * * * * * SecondItem.Interior.Color = RGB(255, 0, 0)
* * * * *End If
* * * * *RowCount = RowCount + 1
* * * Loop
* *End With
* *ScreenUpdating = True
End Sub
"Ty" wrote:
On Aug 11, 6:11 am, Joel wrote:
The code below assumes theree is a header row. *It is much quicker to add
data to empty rows at the end of the worksheet then to insert rows in the
middle of a worksheet. *The code adds duplicate items from sheet 2 to sheet 1
at the end of sheet 1. *Then sorts sheets 1 by column *A. *finally the code
highlights the duplicate rows in sheet 1.
Sub Duplicates()
* *'
* *' NOTE: The macro assumes there is a header in the both worksheets
* *' * * * The macro starts at row 2 and sort data automatically
* *'
* *ScreenUpdating = False
* *'get first empty row of sheet1
* *With Sheets("Sheet1")
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * NewRow = LastRow + 1
* *End With
* *'find matching rows in sheet 2
* *With Sheets("Sheet2")
* * * RowCount = 2
* * * Do While .Range("A" & RowCount) < ""
* * * * *ID = .Range("A" & RowCount)
* * * * *Employee = .Range("B" & RowCount)
* * * * *'compare - look for ID in Sheet 1
* * * * *With Sheets("Sheet1")
* * * * * * Set c = .Columns("A").Find(what:=ID, _
* * * * * * * *LookIn:=xlValues, lookat:=xlWhole)
* * * * * * If Not c Is Nothing Then
* * * * * * * *'add to end of sheet 1
* * * * * * * *.Range("A" & NewRow) = ID
* * * * * * * *.Range("B" & NewRow) = Employee
* * * * * * * *NewRow = NewRow + 1
* * * * * * End If
* * * * *End With
* * * * *RowCount = RowCount + 1
* * * Loop
* *End With
* *'sort and highlight data
* *RowCount = 2
* *With Sheets("Sheet1")
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * 'sort data by column A
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlascendiing
* * * Do While .Range("A" & RowCount) < ""
* * * * *Set FirstItem = .Range("A" & RowCount)
* * * * *Set SecondItem = .Range("A" & (RowCount + 1))
* * * * *If FirstItem.Value = SecondItem.Value Then
* * * * * * FirstItem.Interior.Color = RGB(255, 0, 0)
* * * * * * SecondItem.Interior.Color = RGB(255, 0, 0)
* * * * *End If
* * * * *RowCount = RowCount + 1
* * * Loop
* *End With
* *ScreenUpdating = True
End Sub
"Ty" wrote:
I have several postings. *All of the answers solved my problem. *Here
is another problem that I can't resolve with just VLOOKUP. *VLOOKUP
only grabs the first line of data from the other sheet.
I'm trying to use the same VB script from my first post(down below)..
I need to look at Sheet #2 in comparison to Sheet #1. *Whenever col
1:sheet2 has matching data, then sheet #1 need to INSERT ROW and copy
sheet2:column 2:cell data to sheet1:column2 plus sheet2:column1:cell
data to sheet1:column1. *All changes will be made on Sheet #1 after
viewing Sheet #2.
More detail:
col 1 in both sheets will have the same type of data. *Example: last 4
SSN.
sheet1
col A
2255
3322
1134
8844
col B
blank
Sheet2
col A
2255
2255
2255
col B
Ty
Lincoln
Tony
Sub Duplicates()
* *'
* *' NOTE: You must select the first cell in the column and
* *' make sure that the column is sorted before running this macro
* *'
* *ScreenUpdating = False
* *FirstItem = ActiveCell.Value
* *SecondItem = ActiveCell.Offset(1, 0).Value
* *Offsetcount = 1
* *Do While ActiveCell < ""
* * * If FirstItem = SecondItem Then
* * * * ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0,
0)
* * * * ActiveCell.Offset(Offsetcount - 1, 0).Interior.Color = RGB
(255, 0, 0)
* * * * Offsetcount = Offsetcount + 1
* * * * SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
* * * Else
* * * * ActiveCell.Offset(Offsetcount, 0).Select
* * * * FirstItem = ActiveCell.Value
* * * * SecondItem = ActiveCell.Offset(1, 0).Value
* * * * Offsetcount = 1
* * * End If
* *Loop
* *ScreenUpdating = True
End Sub
I understand this might not be clear the first time around to the
reader. *If not, please ask questions. *Thanks in advance.- Hide quoted text -
- Show quoted text -
I'm stepping through the above with F8 and I have a Watch on Employee
and ID. *I can see the value changing
...
read more »- Hide quoted text -
- Show quoted text -
Thanks for your help. I was tired last night. I'm sorry for the
confusion. Sheet 2 is ONLY used for the comparison. ONLY need 1
column of data from Sheet 2. Sheet 1 is the main source of
information. What you had was working ok except for the data to the
right Col C through Col H starting from Row 2 on Sheet 1.
Management is only interested in all of Sheet 1 data plus the 1 column
of data from Sheet 2.
I really appreciate your patience and assistance.