robsimons
10-24-2007, 07:06 AM
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
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