Duplicate or EXTRA rows

[robsimons]

Hello Guys,

So I got a probably not unique problem...

I have spreadsheet that has 2 worksheets. Both worksheets have the same structure. Actually the worksheets are the same just different months. Each month I need to be able to find the differences between the two sheets... However with 3000+ rows and 10+ columns and the possiblity for each cell to change the differences can be enormos.. This is not what I really want to do.

So what I have is a piece of code that finds the NEW (not in the older file) SSNs. It's at the end.

So just simply my two worksheets look like this... (All actual SSN's are withheld to protect the innocent:giggle )

WKSH1 WKSH2
RW SSN Name SSN NAME
1 12 john 12 john
2 12 john 12 john
3 14 mary 12 john
4 14 mary 12 john
5 16 smith 14 mary
6 16 smith
7 17 ronald


OK so the code I have will find the SSN 17 only....

I'm looking for / or help with a piece of code that will simpley identify that
WKSH1 has "X" rows for a given SSN, but WKSH2 has "X+y" for the same SSN... I need to be able to get the "+y" rows (only) into another worksheet.

Here's my New(unique) people code...

Set rngData = Sheets(shtName2).Range("A2", Sheets(shtName2).[A2].End(xlDown))
Set rngResult = Sheets(shtName).Range("A2")
For Each cell In rngData.Cells
If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A:A"), cell) = 0 Then
i = i + 1
'Sheets(shtName2).Rows(cell.Row).Copy
Sheets(shtName2).Rows(cell.Row).Cut
Sheets(shtName).Activate
rngResult.Offset(i, 0).Select
ActiveSheet.Paste
End If
Next

So any questions? Any help would be greatly appreciated.

Thanks

[Bob Phillips]

Is there any other data that makes rows unique, or do you just copy off howevere many there is?

Is the a move or copy of the +y rows?

[robsimons]

Currently there is nothing else that marks the rows unique... but I'm open all solutions. Yes just copy off what ever "+y" is for that SSN.

I would like to start with copy and then be able to switch to a cut later on. I'm looking for something able to expand as the tables(files) expand.

This is so I can narrow the "changes" down to something managable for the end user.

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim num1 As Long
Dim num2 As Long
Dim NextRow As Long

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
NextRow = 1
For i = 1 To iLastRow
num1 = Application.CountIf(.Columns(1), .Cells(i, "A").Value)
num2 = Application.CountIf(Worksheets("Sheet2").Columns(1), .Cells(i, "A").Value)
If num2 > num1 Then
.Rows(i).Resize(num2 - num1).Copy Worksheets("Sheet3").Cells(NextRow, "A")
NextRow = NextRow + num2 - num1
End If
Do
i = i + 1
Loop Until .Cells(i, "A").Value <> .Cells(i - 1, "A").Value
i = i - 1
Next i

End With

End Sub

[robsimons]

That's great!!! Thanks you for the help....

[robsimons]

So I started doing some testing with this code and what I found was that it was just taking the rows out of Sheet1 and copying them to Sheet3.

I need to take the EXTRA rows from Sheet2, which should be, the last rows with that SSN in Sheet2 and copying them to Sheet3.

For example:
WKSH1
SSN.......NAME........DATE
1...........john..........10/14
1...........john..........11/14

WKSH2
SSN.......NAME........DATE
1...........john..........10/14
1...........john..........11/14
1...........john..........12/14
1...........john..........01/14


The rows I need to copy across are then last two from WKSHT2. And this is going to be everytime...

In the above code, I like it alot, it seems that you always copy the "difference" in rows from Sheet1... I need the "EXTRA" rows from sheet2 copied.

I've tried so mods but nothing works... I'll be back in the morning.

[Bob Phillips]

Is there any other data that makes rows unique, or do you just copy off howevere many there is?


Currently there is nothing else that marks the rows unique... but I'm open all solutions. Yes just copy off what ever "+y" is for that SSN.

[robsimons]

Right, but the "+y" is from worksheet2, your original solution copies the difference in the count of the rows from worksheet1, basically "X" not "+y".

I need the EXTRA rows from worksheet2... Any help would be appreciated.