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.

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.

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 from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.

On Aug 11, 11:19*am, 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 from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.- Hide quoted text -

- Show quoted text -

My mistake. I forgot to match my Sheet2;colA with sheet1:colA. Now,
I have something to work with and I'm having a hard time figuring out
how to modify this code to resolve my problem. It's working but I
have 3 problems

1. it errors out when it tries to do the sort. But I figured that
out. 2 i's in the "xlascendiing". It sorts it and colors but it
still has the original row with the id from up top with a blank cell
in column B and the other 7-10 columns of data to the right. This
leads into problem #2.

.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlascendiing

2. I don't mind adding at the end of the current data but I have
about 7-10 columns of data on Sheet1 to the right of each id such as
NAME, Department, Dpt Number...etc. Since it is hard to insert rows
and easier to add at the end. Is it possible to just place my 200
rows with the additional data on Sheet 3?

p.s.-- This way I don't have to figure out if I need to delete up to
row 155, 255 or 500 and keep the new data.

3. I don't really care for the color part of the code for this
spreadsheet. It can be deleted.

Thanks for your help...

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 from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.

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 from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.- Hide quoted text -

- Show quoted text -

It did the exact same thing as the other code. I made a mistake on
the first code.

What about my problem #2?

2. I don't mind adding at the end of the current data but I have
about 7-10 columns of ADDITIONAL DATA on Sheet1 to the right of each
id such as
NAME, Department, Dpt Number...etc. Since it is hard to insert rows
and easier to add at the end.

Is it possible to just place my 200 rows with the ADDITIONAL DATA on
Sheet 3?

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 from what is on Sheet2 but it is
not adding anything at the end of Sheet 1.- Hide quoted text -

- Show quoted text -

It did the exact same thing as the other code. I made a mistake on
the first code.

What about my problem #2?

2. I don't mind adding at the end of the current data but I have
about 7-10 columns of ADDITIONAL DATA on Sheet1 to the right of each
id such as
NAME, Department, Dpt Number...etc. Since it is hard to insert rows
and easier to add at the end.

Is it possible to just place my 200 rows with the ADDITIONAL DATA on
Sheet 3?