ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VLOOKUP Insert & Copy (https://www.excelbanter.com/excel-programming/432261-vlookup-insert-copy.html)

Ty
VLOOKUP Insert & Copy
 
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.

joel
VLOOKUP Insert & Copy
 
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.

Ty
VLOOKUP Insert & Copy
 
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.

Ty
VLOOKUP Insert & Copy
 
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...

joel
VLOOKUP Insert & Copy
 
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.

Ty
VLOOKUP Insert & Copy
 
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?

joel
VLOOKUP Insert & Copy
 
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?

Ty
VLOOKUP Insert & Copy
 
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 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?- Hide quoted text -

- Show quoted text -

Sheet 1 is the main source of information. I need the whole row of
data on Sheet 1 to the right Col C to Col H that matches the person.
So, When the comparison is done on Col A on Sheet 1 to Sheet2 ColA,
Sheet1, Col C to Col H data need to be placed in the Row.

joel
VLOOKUP Insert & Copy
 
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 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?- Hide quoted text -

- Show quoted text -

Sheet 1 is the main source of information. I need the whole row of
data on Sheet 1 to the right Col C to Col H that matches the person.
So, When the comparison is done on Col A on Sheet 1 to Sheet2 ColA,
Sheet1, Col C to Col H data need to be placed in the Row.

Ty
VLOOKUP Insert & Copy
 
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.

joel
VLOOKUP Insert & Copy
 
Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:

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.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 6:22*am, Joel wrote:
Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.



"Ty" wrote:
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 =

...

read more »- Hide quoted text -

- Show quoted text -

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.

joel
VLOOKUP Insert & Copy
 
The best way of doing this is to copy sheet1 to sheet 3. Then add sheet 2 to
end of sheet 3. Next sort sheet 3 by column A.

Now we must get rid of non duplicates. So I check if the column A data
match the next and previous rows and place an X in Column IV. So column IV
now contains an X on the rows to delete. I use autofilter to get only the
X's and delete these rows. See code below.


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

'copy sheet 1 to sheet 3
With Sheets("Sheet3")
Sheets("Sheet1").Cells.Copy _
Destination:=.Cells

'find last row
LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

If LastRowA LastRowB Then
LastRow = LastRowA
Else
LastRow = LastRowB
End If

NewRow = LastRow + 1

With Sheets("Sheet2")
'find last row
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With

'copy sheet 2 to end of sheet 3
Sheets("Sheet2").Rows("1:" & LastRow).Copy _
Destination:=.Rows(NewRow)

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


'Mark row which aren't duplicates so they can be removed

RowCount = 3
Do While .Range("A" & RowCount) < ""
'check if ID matches either previous or next row
If .Range("A" & RowCount) < .Range("A" & (RowCount - 1)) And _
.Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then

.Range("IV" & RowCount) = "X"

End If
RowCount = RowCount + 1
Loop

'put anything in cell IV1 so filter works properly
.Range("IV1") = "Anything"
'filter on x's
.Columns("IV:IV").AutoFilter
.Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

Set VisibleRows = .Rows("2:" & LastRow) _
.SpecialCells(xlCellTypeVisible)
'delete rows with X's
VisibleRows.Delete
'turn off autfilter
.Columns("IV:IV").AutoFilter
'clear IV1
.Range("IV1").Clear
End With

ScreenUpdating = True

End Sub


"Ty" wrote:

On Aug 12, 6:22 am, Joel wrote:
Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.



"Ty" wrote:
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 =

...

read more »- Hide quoted text -

- Show quoted text -

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 10:15*am, Ty wrote:
On Aug 12, 6:22*am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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.

joel
VLOOKUP Insert & Copy
 
I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending


To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

"Ty" wrote:

On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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 =

...

read more »- Hide quoted text -

- Show quoted text -

Hope you are in the best of moods today. I really apologize for all
of the confusion. I don't want to repeat what I said before because I
have a better understanding of what needs to be done. Manually, I was
doing inserts/copy/paste. It is difficult to do inserts. Not
impossible but difficult. I'm gonna follow your logic for the code.
What you did on the first code was ok except for the following:

On sheet 1 when your first code was done. The changes on Sheet 1 were
one line below the original data on Sheet 1. Example:

One way of visually showing the SS:
ColA ColB ColC
ID1 blank cell [all of the data from Col C to Col H(actually P)]
ID1 data(from Sheet2) [no data]
ID1 data(from Sheet2) [no data]

Another way of visually representing the SS:
Row 2: ColA=ID1 ColB=blank cell ColC:ColH=[all of the data from Col
C to Col H(actually P)]
Row 3: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]
Row 4: ColA=ID1 ColB=data(from Sheet2) ColC:ColH=[no data]

After the sort is done, there is essentially 1 extra ID/Employee being
displayed because you have the original id on Sheet 1 and the end
result of the code. Manually, I was just inputting the Data from
Sheet 2 in the blank cell. Then ONLY if there was extra Data I was
inserting a row and inputting the other 2 or 3 cells/rows.

So, basically Row 3-4 is the end results of the code. Which is
GREAT! I just wanted those results to include [all of the data from
Col C to Col P]. If possible, ONLY place the end results on Sheet 3
with the Sheet1: ColC:ColP data matching each id.

If that is not clear, I will try to answer your questions first before
you display code.

Again, Thank you.

I like the earlier code. I didn't step through this new code but the
end result looks like a copy of Sheet 1 and Sheet 2 on Sheet 3. On
Sheet 3, Sheet 1 data is up top and Sheet 2 data is at the bottom.

The earlier code was OK. The end results before the sort &
highlighting was exactly what was needed. It just did not have the
data from Sheet1 Col C:Col P.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 12:50*pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending



"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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.

...

read more »- Hide quoted text -

- Show quoted text -

I'm sorry..I'm being difficult and breaking a record for the most
post.

I made the change. The code merges the 2 sheets together on Sheet 3
but the first line of each match looks like the old code. I contine
to like the old code but it need to insert the whole line of data from
sheet 1 on the first match. The other matches can stay blank.

Regards,
Ty

joel
VLOOKUP Insert & Copy
 
Are all the columns displayed on Sheet3? The code is copying the entire row
from sheet 1. You said in one of your previous postings that column A on
sheet1 wasn't filled in. Is this the cause of the problem. I bet you some
of the rows on sheet 3 have data in columns C - H and some don't. Make sure
no rows and columns are hidden in sheet 3.

"Ty" wrote:

On Aug 12, 12:50 pm, Joel wrote:
I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending



"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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.

...

read more »- Hide quoted text -

- Show quoted text -

I'm sorry..I'm being difficult and breaking a record for the most
post.

I made the change. The code merges the 2 sheets together on Sheet 3
but the first line of each match looks like the old code. I contine
to like the old code but it need to insert the whole line of data from
sheet 1 on the first match. The other matches can stay blank.

Regards,
Ty

joel
VLOOKUP Insert & Copy
 
One other possiblility. The only rows in Sheet 1 that have data in columns c
- h don't have duplicates so they are being deleted. It is possible is the
data in column A in sheets 1 & 2 don't match EXACTLY then the rows from sheet
1 will be deleted. Make sure there ae no spaces in the column A data on both
sheets A & B. Also make sure leading zeroes ae the same. And make sure
there aen't single quotes in front of the numbers on either sheet 1 or sheet
2.

A quick check is to see if any of the data in column A (both sheets 1 & 2)
arre formated as either General or Number (not text).

"Ty" wrote:

On Aug 12, 12:50 pm, Joel wrote:
I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending



"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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.

...

read more »- Hide quoted text -

- Show quoted text -

I'm sorry..I'm being difficult and breaking a record for the most
post.

I made the change. The code merges the 2 sheets together on Sheet 3
but the first line of each match looks like the old code. I contine
to like the old code but it need to insert the whole line of data from
sheet 1 on the first match. The other matches can stay blank.

Regards,
Ty

joel
VLOOKUP Insert & Copy
 
One last idea. Are there any formulas in Column A (sheet 1 or 2). I may
need to copy and paste special values to eliminate the problem.

"Ty" wrote:

On Aug 12, 12:50 pm, Joel wrote:
I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending



"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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.

...

read more »- Hide quoted text -

- Show quoted text -

I'm sorry..I'm being difficult and breaking a record for the most
post.

I made the change. The code merges the 2 sheets together on Sheet 3
but the first line of each match looks like the old code. I contine
to like the old code but it need to insert the whole line of data from
sheet 1 on the first match. The other matches can stay blank.

Regards,
Ty

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 2:21*pm, Joel wrote:
One last idea. *Are there any formulas in Column A (sheet 1 or 2). *I may
need to copy and paste special values to eliminate the problem.



"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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) < ""

...

read more »- Hide quoted text -

- Show quoted text -

There are no formula's. It's just that the new code is doing almost
exactly the same as the original code except adding Sheet 2 additional
information. Here I my steps when I do it manually:

1. I inserted Col B next to the ID.
2. Then I copied the Data from sheet 2 in the cell next to the id on
Sheet 1.
3. If there was more data matching the id on Sheet 1, I will insert
another row and add that data.

Note: I really would like to see the cell next to the id filled in
with the data from Sheet 2. I wish I can email you a screen shot.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 3:04*pm, Ty wrote:
On Aug 12, 2:21*pm, Joel wrote:



One last idea. *Are there any formulas in Column A (sheet 1 or 2). *I may
need to copy and paste special values to eliminate the problem.

"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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

...

read more »- Hide quoted text -

- Show quoted text -

"You said in one of your previous postings that column A on
sheet1 wasn't filled in." Correction I said cells in Column B were
not filled in. Every cell in Column B should be filled in.

All of the columns C through P are displayed. Which is OK. It is
just that again(repeate), the cell on Col B next to the data on the
ROW is blank. Again, when I did it manually, I would just copy and
past the data into B2.

joel
VLOOKUP Insert & Copy
 
Lets try again.

1) Do you like the two rows size row approach. One long row(from sheet A)
and one short (data from sheet 1) ?
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B. I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2). Then replace the formula using PasteSpecial.

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

'copy sheet 1 to sheet 3
With Sheets("Sheet3")
Sheets("Sheet1").Cells.Copy _
Destination:=.Cells

'find last row
LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

If LastRowA LastRowB Then
LastRow = LastRowA
Else
LastRow = LastRowB
End If

NewRow = LastRow + 1

With Sheets("Sheet2")
'find last row
LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
End With

'copy sheet 2 to end of sheet 3
Sheets("Sheet2").Rows("1:" & LastRow2).Copy _
Destination:=.Rows(NewRow)

'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending


'Mark row which aren't duplicates so they can be removed

RowCount = 3
Do While .Range("A" & RowCount) < ""
'check if ID matches either previous or next row
If .Range("A" & RowCount) < .Range("A" & (RowCount - 1)) And _
.Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then

.Range("IV" & RowCount) = "X"

End If
RowCount = RowCount + 1
Loop

'put anything in cell IV1 so filter works properly
.Range("IV1") = "Anything"
'filter on x's
.Columns("IV:IV").AutoFilter
.Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

Set VisibleRows = .Rows("2:" & LastRow) _
.SpecialCells(xlCellTypeVisible)
'delete rows with X's
VisibleRows.Delete
'turn off autfilter
.Columns("IV:IV").AutoFilter
'clear IV1
.Range("IV1").Clear


'add formual in column B to get data from sheet 2
.Range("B2").Formula = _
"=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'copy formula down column B
.Range("B2").Copy _
Destination:=.Range("B2:B" & LastRow)

'replace formula with data
.Columns("B").Copy
.Columns("B").PasteSpecial _
Paste:=xlPasteValues

End With

ScreenUpdating = True

End Sub


"Ty" wrote:

On Aug 12, 2:21 pm, Joel wrote:
One last idea. Are there any formulas in Column A (sheet 1 or 2). I may
need to copy and paste special values to eliminate the problem.



"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. the code didn't sort enough rows so it kept the data
seperated.

From
'Sort Data
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

To
'Sort Data
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
Key1:=.Range("A1"), _
order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. I'm not sure which code you need modified.
Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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) < ""

...

read more »- Hide quoted text -

- Show quoted text -

There are no formula's. It's just that the new code is doing almost
exactly the same as the original code except adding Sheet 2 additional
information. Here I my steps when I do it manually:

1. I inserted Col B next to the ID.
2. Then I copied the Data from sheet 2 in the cell next to the id on
Sheet 1.
3. If there was more data matching the id on Sheet 1, I will insert
another row and add that data.

Note: I really would like to see the cell next to the id filled in
with the data from Sheet 2. I wish I can email you a screen shot.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 4:05*pm, Joel wrote:
Lets try again. *

1) Do you like the two rows size row approach. *One long row(from sheet A)
and one short (data from sheet 1) ? *
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B. *I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2). *Then replace the formula using *PasteSpecial.

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

* *'copy sheet 1 to sheet 3
* *With Sheets("Sheet3")
* * * Sheets("Sheet1").Cells.Copy _
* * * * *Destination:=.Cells

* * * 'find last row
* * * LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
* * * LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

* * * If LastRowA LastRowB Then
* * * * *LastRow = LastRowA
* * * Else
* * * * *LastRow = LastRowB
* * * End If

* * * NewRow = LastRow + 1

* * * With Sheets("Sheet2")
* * * * *'find last row
* * * * *LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
* * * End With

* * * 'copy sheet 2 to end of sheet 3
* * * Sheets("Sheet2").Rows("1:" & LastRow2).Copy _
* * * * *Destination:=.Rows(NewRow)

* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

* * * 'Mark row which aren't duplicates so they can be removed

* * * RowCount = 3
* * * Do While .Range("A" & RowCount) < ""
* * * * *'check if ID matches either previous or next row
* * * * *If .Range("A" & RowCount) < .Range("A" & (RowCount - 1)) And _
* * * * * * .Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then

* * * * * * .Range("IV" & RowCount) = "X"

* * * * *End If
* * * * *RowCount = RowCount + 1
* * * Loop

* * * 'put anything in cell IV1 so filter works properly
* * * .Range("IV1") = "Anything"
* * * 'filter on x's
* * * .Columns("IV:IV").AutoFilter
* * * .Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

* * * Set VisibleRows = .Rows("2:" & LastRow) _
* * * * *.SpecialCells(xlCellTypeVisible)
* * * 'delete rows with X's
* * * VisibleRows.Delete
* * * 'turn off autfilter
* * * .Columns("IV:IV").AutoFilter
* * * 'clear IV1
* * * .Range("IV1").Clear

* * * 'add formual in column B to get data from sheet 2
* * * .Range("B2").Formula = _
* * * * *"=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * 'copy formula down column B
* * * .Range("B2").Copy _
* * * * *Destination:=.Range("B2:B" & LastRow)

* * * 'replace formula with data
* * * .Columns("B").Copy
* * * .Columns("B").PasteSpecial _
* * * * *Paste:=xlPasteValues

* *End With

* *ScreenUpdating = True

End Sub



"Ty" wrote:
On Aug 12, 2:21 pm, Joel wrote:
One last idea. *Are there any formulas in Column A (sheet 1 or 2). *I may
need to copy and paste special values to eliminate the problem.

"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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 +

...

read more »- Hide quoted text -

- Show quoted text -

I'm excited!! Very close. The only problem now is that it placed
some more columns from Sheet2 on the other rows/cells that are suppose
to be empty. In others words it did not just ONLY use Column B. If
that can't be solved, I can solve that by deleting those col/rows from
sheet2. I don't need them anyways.

Ty
VLOOKUP Insert & Copy
 
On Aug 12, 4:38*pm, Ty wrote:
On Aug 12, 4:05*pm, Joel wrote:



Lets try again. *

1) Do you like the two rows size row approach. *One long row(from sheet A)
and one short (data from sheet 1) ? *
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B. *I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2). *Then replace the formula using *PasteSpecial.

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

* *'copy sheet 1 to sheet 3
* *With Sheets("Sheet3")
* * * Sheets("Sheet1").Cells.Copy _
* * * * *Destination:=.Cells

* * * 'find last row
* * * LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
* * * LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

* * * If LastRowA LastRowB Then
* * * * *LastRow = LastRowA
* * * Else
* * * * *LastRow = LastRowB
* * * End If

* * * NewRow = LastRow + 1

* * * With Sheets("Sheet2")
* * * * *'find last row
* * * * *LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
* * * End With

* * * 'copy sheet 2 to end of sheet 3
* * * Sheets("Sheet2").Rows("1:" & LastRow2).Copy _
* * * * *Destination:=.Rows(NewRow)

* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

* * * 'Mark row which aren't duplicates so they can be removed

* * * RowCount = 3
* * * Do While .Range("A" & RowCount) < ""
* * * * *'check if ID matches either previous or next row
* * * * *If .Range("A" & RowCount) < .Range("A" & (RowCount - 1)) And _
* * * * * * .Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then

* * * * * * .Range("IV" & RowCount) = "X"

* * * * *End If
* * * * *RowCount = RowCount + 1
* * * Loop

* * * 'put anything in cell IV1 so filter works properly
* * * .Range("IV1") = "Anything"
* * * 'filter on x's
* * * .Columns("IV:IV").AutoFilter
* * * .Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

* * * Set VisibleRows = .Rows("2:" & LastRow) _
* * * * *.SpecialCells(xlCellTypeVisible)
* * * 'delete rows with X's
* * * VisibleRows.Delete
* * * 'turn off autfilter
* * * .Columns("IV:IV").AutoFilter
* * * 'clear IV1
* * * .Range("IV1").Clear

* * * 'add formual in column B to get data from sheet 2
* * * .Range("B2").Formula = _
* * * * *"=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * 'copy formula down column B
* * * .Range("B2").Copy _
* * * * *Destination:=.Range("B2:B" & LastRow)

* * * 'replace formula with data
* * * .Columns("B").Copy
* * * .Columns("B").PasteSpecial _
* * * * *Paste:=xlPasteValues

* *End With

* *ScreenUpdating = True

End Sub

"Ty" wrote:
On Aug 12, 2:21 pm, Joel wrote:
One last idea. *Are there any formulas in Column A (sheet 1 or 2).. *I may
need to copy and paste special values to eliminate the problem.

"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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

...

read more »- Hide quoted text -

- Show quoted text -

On 2nd review this morning. It's not working.

In response to one of the questions above. Yes, the problem was that
Col B was not being filled in. The problem today: The 2nd id is
being placed as all of the id's for that Employee. The 3rd problem is
the number of id's is a little off for some of them towards the
bottom. For example, 1 employee should have 1 id but they have 2.
I'm gonna step through the code.

Ty
VLOOKUP Insert & Copy
 
On Aug 13, 10:48*am, Ty wrote:
On Aug 12, 4:38*pm, Ty wrote:



On Aug 12, 4:05*pm, Joel wrote:

Lets try again. *

1) Do you like the two rows size row approach. *One long row(from sheet A)
and one short (data from sheet 1) ? *
2) Was the problem just that column B was not filled in with the data from
sheet 2 for rows that came from sheet 1?

I made some small changes to the code below to add sheet 2 column B into
sheet 3 column B. *I used VLOOKUP formula in column B (sheet 3) to get the
data from sheet 2). *Then replace the formula using *PasteSpecial..

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

* *'copy sheet 1 to sheet 3
* *With Sheets("Sheet3")
* * * Sheets("Sheet1").Cells.Copy _
* * * * *Destination:=.Cells

* * * 'find last row
* * * LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
* * * LastRowB = .Range("B" & Rows.Count).End(xlUp).Row

* * * If LastRowA LastRowB Then
* * * * *LastRow = LastRowA
* * * Else
* * * * *LastRow = LastRowB
* * * End If

* * * NewRow = LastRow + 1

* * * With Sheets("Sheet2")
* * * * *'find last row
* * * * *LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
* * * End With

* * * 'copy sheet 2 to end of sheet 3
* * * Sheets("Sheet2").Rows("1:" & LastRow2).Copy _
* * * * *Destination:=.Rows(NewRow)

* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

* * * 'Mark row which aren't duplicates so they can be removed

* * * RowCount = 3
* * * Do While .Range("A" & RowCount) < ""
* * * * *'check if ID matches either previous or next row
* * * * *If .Range("A" & RowCount) < .Range("A" & (RowCount - 1)) And _
* * * * * * .Range("A" & RowCount) < .Range("A" & (RowCount + 1)) Then

* * * * * * .Range("IV" & RowCount) = "X"

* * * * *End If
* * * * *RowCount = RowCount + 1
* * * Loop

* * * 'put anything in cell IV1 so filter works properly
* * * .Range("IV1") = "Anything"
* * * 'filter on x's
* * * .Columns("IV:IV").AutoFilter
* * * .Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"

* * * Set VisibleRows = .Rows("2:" & LastRow) _
* * * * *.SpecialCells(xlCellTypeVisible)
* * * 'delete rows with X's
* * * VisibleRows.Delete
* * * 'turn off autfilter
* * * .Columns("IV:IV").AutoFilter
* * * 'clear IV1
* * * .Range("IV1").Clear

* * * 'add formual in column B to get data from sheet 2
* * * .Range("B2").Formula = _
* * * * *"=VLOOKUP(A2,Sheet2!A$1:B$" & LastRow2 & ",2)"

* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * 'copy formula down column B
* * * .Range("B2").Copy _
* * * * *Destination:=.Range("B2:B" & LastRow)

* * * 'replace formula with data
* * * .Columns("B").Copy
* * * .Columns("B").PasteSpecial _
* * * * *Paste:=xlPasteValues

* *End With

* *ScreenUpdating = True

End Sub

"Ty" wrote:
On Aug 12, 2:21 pm, Joel wrote:
One last idea. *Are there any formulas in Column A (sheet 1 or 2). *I may
need to copy and paste special values to eliminate the problem.

"Ty" wrote:
On Aug 12, 12:50 pm, Joel wrote:
I left out one line. *the code didn't sort enough rows so it kept the data
seperated.

From
* * * 'Sort Data
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

To
* * * 'Sort Data
* * * LastRow = .Range("A" & Rows.Count).End(xlUp).Row
* * * .Rows("1:" & LastRow).Sort _
* * * * *header:=xlYes, _
* * * * *Key1:=.Range("A1"), _
* * * * *order1:=xlAscending

"Ty" wrote:
On Aug 12, 10:15 am, Ty wrote:
On Aug 12, 6:22 am, Joel wrote:

Can you be a little more specifc. *I'm not sure which code you need modified.
*Repost what you want modified with the description of the change in To/From
format.

"Ty" wrote:
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

...

read more »- Hide quoted text -

- Show quoted text -

I'm slowly stepping through the lines of code. I can see what you are
doing. The following
sections make this sheet look like the end results of the first code.
Column B cell next to
the Col A:cell is blank. Then the End results of the code has been
copied under the Col A
cell with the match in Col B.

'check if ID matches either previous or next row.
'clear IV1

PROBLEM: It's the VLOOKUP section that fills in the blank cell in
ColB:sheet3.---the ID is
the same for all ColB:cells matching the Employee.


All times are GMT +1. The time now is 12:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com