+ Post New Thread
Results 1 to 5 of 5
General Chat Thread, Headache (Spreadsheet related) in General; I have a Spreadsheet, let us call it "Student A". This spreadsheet contains nearly 10,000 rows. I have another Spreadsheets, ...
  1. #1

    Join Date
    Jan 2007
    Posts
    967
    Thank Post
    10
    Thanked 35 Times in 27 Posts
    Rep Power
    22

    Headache (Spreadsheet related)

    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...

  2. #2

    Join Date
    Jul 2011
    Posts
    28
    Thank Post
    2
    Thanked 4 Times in 4 Posts
    Rep Power
    7
    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.

  3. #3

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,689
    Thank Post
    334
    Thanked 515 Times in 483 Posts
    Rep Power
    179
    Quote Originally Posted by googlemad View Post
    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

  4. #4

    Join Date
    Jan 2007
    Posts
    967
    Thank Post
    10
    Thanked 35 Times in 27 Posts
    Rep Power
    22
    I can paste the file into a seperate sheet, yes

  5. #5

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,689
    Thank Post
    334
    Thanked 515 Times in 483 Posts
    Rep Power
    179
    Code:
    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 :P If you want to test it, change the 10000 and 2000 to smaller numbers.

    Steve

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 7th December 2006, 12:27 PM
  2. Replies: 0
    Last Post: 7th December 2006, 12:27 PM
  3. google spreadsheet
    By russdev in forum Educational Software
    Replies: 15
    Last Post: 15th June 2006, 06:42 AM
  4. Office 2003 install headache
    By pooley in forum Windows
    Replies: 8
    Last Post: 2nd November 2005, 04:52 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •