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.