Headache (Spreadsheet related)

[googlemad]

I have a Spreadsheet, let us call it "Student A". This spreadsheet contains nearly 10,000 rows.

 

I have another Spreadsheets, "Student B". This spreadsheet contains nearly 2,000 rows.

 

Due to a balls up by a member of the data analyst team providing us with a report for student logins, we obviously have a lot of logins that will never ever be used as the students go back years! (Imported from "Student A").

 

Somehow I need to create a "Student C" spreadsheet that contains all rows from "Student A" that do not appear in "Student B" so I can then run "Student C" to remove all the 'dead' logins!

 

Any ideas? As my brain is breaking the speed limit right now...

[Rodan]

Two approaches

1. Import each in to access then create an unmatched query through the query wizard.

2. In Excel use a column to do a vlookup and generate a yes/no result in the A spreadsheet to see if it occurs in the B then sort on the result.

 

Hope that helps.

[Steve21]

Any ideas? As my brain is breaking the speed limit right now...

 

Are you allowed to copy and paste them into one "file" but two sheets? Rather than two files, if so it shouldn't be hard, or are they already in same file?

 

Steve

[googlemad]

I can paste the file into a seperate sheet, yes

[Steve21]

Dim iListCount As Integer
Dim iCtr As Integer

Application.ScreenUpdating = False
iListCount = Sheets("sheet2").Range("A1:A10000").Rows.Count

For Each x In Sheets("Sheet1").Range("A1:A2000")
  For iCtr = 1 To iListCount
     If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
        Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
         iCtr = iCtr + 1
     End If
  Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"

 

Will remove all instances of Sheet1 from Sheet2. Leaving a clean copy (aka your sheet C). Obviously will take a long time going through 10k lines, but will work If you want to test it, change the 10000 and 2000 to smaller numbers.

 

Steve