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.