Compare 2 Sheets and Extract Unique Info to a 3rd Sheet

I have two sheets of stock data from a stock screener, both same
parameters, just different time frames (i.e., one sheet is newer
whenever I import new a new screen from the Internet), which will pull
different stocks as time passes. I'm trying to compare the two sheets
and extract the stock data (a whole row's worth of data, not just a
cell) that is unique to the "Last Import" sheet. This would allow any
new stocks identified on the screener to be transferred to a new sheet
(called "Filtered List"). That way I don't have to research the same
stocks over and over, only the new ones that show up with each import.
I did some searching on the Internet and found the following code,
which I modified to include the sheet names:

Sub CompareMove()
'
    Dim lastrowsh1 As Long, lastrowsh2 As Long, lastrowsh3 As Long
    Dim searchRng As Range, foundRng As Range
    Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
    lastrowsh1 = Worksheets("Previous
Import").Range("a65536").End(xlUp).Row
    lastrowsh2 = Worksheets("Last
Import").Range("a65536").End(xlUp).Row
    lastrowsh3 = Worksheets("Filtered
List").Range("a65536").End(xlUp).Row
    Set Ws1 = Worksheets("Previous Import")
    Set Ws2 = Worksheets("Last Import")
    Set Ws3 = Worksheets("Filtered List")

    Set searchRng = Ws2.Columns(1) 'sets column a on Previous Import

    With Ws2
    For x = 5 To lastrowsh2
    Set foundRng = searchRng.Find(Ws1.Cells(x, 1))  'loop through the
previous import
    If Not foundRng Is Nothing Then
     foundRng.EntireRow.Copy  'on a match copy row
     Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
    End If
    Next x
    End With
End Sub

This is almost what I'm looking for, right now when it runs it extracts
the data COMMON to both sheets, rather than what is UNIQUE which is
what I need it to do.  It looks like I'm sooooo close...can someone
tell me what code I need to modify to make it do this?  I don't have
too much knowledge outside of basic macro building, and I would be very
grateful for ANY assistance y'all could provide.

Thanks in advance...

0
kilo94 (14)
12/19/2005 1:38:04 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
627 Views

Similar Articles

[PageSpeed] 39

This line:

If Not foundRng Is Nothing Then

Essentially says "If foundrng is something"--that the previous find was
successful, then do the work.

Since you want to do the work if it wasn't found, try removing "not" from that
line:

If foundRng Is Nothing Then



kilo1990 wrote:
> 
> I have two sheets of stock data from a stock screener, both same
> parameters, just different time frames (i.e., one sheet is newer
> whenever I import new a new screen from the Internet), which will pull
> different stocks as time passes. I'm trying to compare the two sheets
> and extract the stock data (a whole row's worth of data, not just a
> cell) that is unique to the "Last Import" sheet. This would allow any
> new stocks identified on the screener to be transferred to a new sheet
> (called "Filtered List"). That way I don't have to research the same
> stocks over and over, only the new ones that show up with each import.
> I did some searching on the Internet and found the following code,
> which I modified to include the sheet names:
> 
> Sub CompareMove()
> '
>     Dim lastrowsh1 As Long, lastrowsh2 As Long, lastrowsh3 As Long
>     Dim searchRng As Range, foundRng As Range
>     Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
>     lastrowsh1 = Worksheets("Previous
> Import").Range("a65536").End(xlUp).Row
>     lastrowsh2 = Worksheets("Last
> Import").Range("a65536").End(xlUp).Row
>     lastrowsh3 = Worksheets("Filtered
> List").Range("a65536").End(xlUp).Row
>     Set Ws1 = Worksheets("Previous Import")
>     Set Ws2 = Worksheets("Last Import")
>     Set Ws3 = Worksheets("Filtered List")
> 
>     Set searchRng = Ws2.Columns(1) 'sets column a on Previous Import
> 
>     With Ws2
>     For x = 5 To lastrowsh2
>     Set foundRng = searchRng.Find(Ws1.Cells(x, 1))  'loop through the
> previous import
>     If Not foundRng Is Nothing Then
>      foundRng.EntireRow.Copy  'on a match copy row
>      Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>     End If
>     Next x
>     End With
> End Sub
> 
> This is almost what I'm looking for, right now when it runs it extracts
> the data COMMON to both sheets, rather than what is UNIQUE which is
> what I need it to do.  It looks like I'm sooooo close...can someone
> tell me what code I need to modify to make it do this?  I don't have
> too much knowledge outside of basic macro building, and I would be very
> grateful for ANY assistance y'all could provide.
> 
> Thanks in advance...

-- 

Dave Peterson
0
petersod (12004)
12/19/2005 4:09:01 PM
I took out the "not" per your suggestion and now the following error
comes up:

Runtime error '91':  Object Variable or With Block variable not set

Then the following line is highlighted:
     foundRng.EntireRow.Copy  'on a match copy row

What's next?

0
kilo94 (14)
12/19/2005 7:05:52 PM
Yep.  My mistake.

What do you want to copy?  The cell with the value that you're searching for?



    With Ws2
     For x = 5 To lastrowsh2
       Set foundRng = searchRng.Find(Ws1.Cells(x, 1))
       If Not foundRng Is Nothing Then
         ws1.rows(x).Copy  
         Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
      End If
     Next x
    End With

You may have to change your ranges around to loop through the cells on ws1 and
compare then with searchrng????

kilo1990 wrote:
> 
> I took out the "not" per your suggestion and now the following error
> comes up:
> 
> Runtime error '91':  Object Variable or With Block variable not set
> 
> Then the following line is highlighted:
>      foundRng.EntireRow.Copy  'on a match copy row
> 
> What's next?

-- 

Dave Peterson
0
petersod (12004)
12/19/2005 7:15:45 PM
I'm wanting to copy the rows that are unique to the "Last Import" sheet
beginning at A6.  The "Previous Import" sheet will be yesterday's data.
 The "Last Import" tab will have some stocks from the previous day's
trading, but I'm only interested in the new stocks that hit today
(hence the "Filtered List" sheet).  I'm not sure what todo, I pasted
your last code in there and it still transferring the data common to
both sheets, as opposed to what is unique in the "Last Import" tab.
HELP....

0
kilo94 (14)
12/19/2005 8:05:33 PM
Doh.

I changed the code, but I forgot to remove the "NOT" from that check:

    With Ws2
     For x = 5 To lastrowsh2
       Set foundRng = searchRng.Find(Ws1.Cells(x, 1))
       If foundRng Is Nothing Then
         ws1.rows(x).Copy  
         Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
      End If
     Next x
    End With

=======
Here's one that looks at differences between either list:

Option Explicit

Sub CompareMove()

    Dim lastrowSh1 As Long
    Dim lastrowSh2 As Long
    Dim lastrowSh3 As Long
    
    Dim X As Long
    
    Dim foundRng As Range
    
    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Dim Ws3 As Worksheet
    
    Set Ws1 = Worksheets("Previous Import")
    Set Ws2 = Worksheets("Last Import")
    Set Ws3 = Worksheets("Filtered List")
    
    lastrowSh1 = Ws1.Range("a65536").End(xlUp).Row
    lastrowSh2 = Ws2.Range("a65536").End(xlUp).Row
    lastrowSh3 = Ws3.Range("a65536").End(xlUp).Row

    With Ws2
        For X = 5 To lastrowSh1
            With .Columns(1)
                Set foundRng = .Find(what:=Ws1.Cells(X, 1), _
                                    after:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    lookat:=xlWhole, _
                                    searchorder:=xlByRows, _
                                    searchdirection:=xlNext, _
                                    MatchCase:=False)
            End With
            
            If foundRng Is Nothing Then
                Ws1.Rows(X).Copy  'on a match copy row
                Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
            End If
        Next X
    End With
    
    With Ws1
        For X = 5 To lastrowSh2
            With .Columns(1)
                Set foundRng = .Find(what:=Ws2.Cells(X, 1), _
                                    after:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    lookat:=xlWhole, _
                                    searchorder:=xlByRows, _
                                    searchdirection:=xlNext, _
                                    MatchCase:=False)
            End With
            
            If foundRng Is Nothing Then
                Ws2.Rows(X).Copy  'on a match copy row
                Ws3.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
            End If
        Next X
    End With

End Sub


If you don't want both sets of differences, then delete one of them.

ps.  When you're doing .find's in code, it's better to specify exactly what you
want.  Excel & VBA remembers the last thing that was used--either by the user or
by code.



kilo1990 wrote:
> 
> I'm wanting to copy the rows that are unique to the "Last Import" sheet
> beginning at A6.  The "Previous Import" sheet will be yesterday's data.
>  The "Last Import" tab will have some stocks from the previous day's
> trading, but I'm only interested in the new stocks that hit today
> (hence the "Filtered List" sheet).  I'm not sure what todo, I pasted
> your last code in there and it still transferring the data common to
> both sheets, as opposed to what is unique in the "Last Import" tab.
> HELP....

-- 

Dave Peterson
0
petersod (12004)
12/19/2005 9:15:18 PM
WOW, DAVE, THEY BOTH WORK.  Both the original code AND the one you
suggested...this is GREAT.  Thanks so much for taking the time!

0
kilo94 (14)
12/19/2005 10:06:27 PM
Sorry, it took so long to get it straight.

But glad it worked out.

kilo1990 wrote:
> 
> WOW, DAVE, THEY BOTH WORK.  Both the original code AND the one you
> suggested...this is GREAT.  Thanks so much for taking the time!

-- 

Dave Peterson
0
petersod (12004)
12/19/2005 10:36:35 PM
Reply:

Similar Artilces:

delay #2
Hi, I use E2K3 in this configuration : 1 FE; 1 BE My FE is in DMZ,when my user open thir Outlook 2000 they received an POP, in this popup, request Exchnage for found information, i read my FE Name. Have you an idae ? for help to find my pb. Rem : after this delay , i have'nt any pb for use outlook or on my BE; Thks Sdeseals ...

lists #2
Hi I have created a drop down list in excel which is attached to a table (via VLOOKUP). The drop down list contains a code number. What I would like is a comment next to the list saying what each code represents (eg 100 - stationery) but only putting in the value when selected (as it is part of a formula). does anyone know how to do this? any help appreciated. kind regards Rexmann Kind regards Gareth Hi! If this is a listbox from the Control Toolbox then you can do this: Call up the listbox's Properties Select ColumnCount and enter 2 Select ColumnWidths eg 30, 100 (depends on you...

Copy Purchase orders #2
Does any one know how to copy a purchase order? We do booking orders and have different ship dates. I would like to break up my PO and not have to enter items 2 - 3 times. Thanks No-one answered this one yet. Shame, because I could do with knowing. We very often want to produce a PO for 8 branch, all identical. Please help. "lax guy" <laxguy@discussions.microsoft.com> wrote in message news:C284C5BF-7A6E-45CA-A9A0-88871B48E3B0@microsoft.com... > Does any one know how to copy a purchase order? > > We do booking orders and have different ship dates. I would like...

multiple emails #2
recently whenever i send an email to someone, they receive at least 2 copies of it, sometimes 3, i just noticed that when i am sending, the in process shows 2 processes for each email that i send. anybody got any ideas. ...

Pivot Chart #2
Hi. I have Excel 97 at work. I have a Pivot Table. I want to make charts for items that their grand total is more than 100, between 10 and hundred and less than hundred. So everytime the pivot table is updated the charts will update themselves. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

handle #2
Hi, How should i get the handle of the print dialog that comes up when i print a file using ShellExecutEx()...? "gshetty" <gshetty85@gmail.com> wrote in message news:1193068049.692260.78380@q3g2000prf.googlegroups.com... > Hi, > > How should i get the handle of the print dialog that comes up when i > print a file using ShellExecutEx()...? > There's no straightforward way to get it, but this is a reasonable hack: Use Active Accessibility (SetWinEventHook) to be notified of new window creations, and identify the print dialog with either the classname or...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Transaction Logs #2
Hi everyone, I've installed Exchange 2003 in my environment.and now there are a lot of E00XXXXX.log files filling up my hard disk. I've read and heard that after a fully backup those files would be erased, but it does not happen. Do I need to configure something? Tks in advance, Marco Please detail how you are performing a full backup. In order to purge the log files, you need to be performing an Exchange-aware full online backup (Exchange services still running). NTBackup on the Exchange server is Exchange-aware. 3rd party products such as BackupExec are only ...

CFileDialog customization #2
I have my own class I have derived from CFileDialog and I want to filter the "Look In" combo box so it only contains the folders I want the user to be able to navigate to. I can easily gain access to the CComboBox but I'm having difficulties filtering the contents. I can't really go from the text of the item, because I really need the full path. There is a trick with the list control of the file dialog in that each item display has its PIDL stored in the lParam of the list item. I was hoping there would be a similar trick to the items in the combobox. I can do a GetItemD...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Posting Status #2
I have a PMPAY batch where the status is 'Receiving' these are configured to post through to the GL. See KB850289 for details on how to fix that. "rcr" wrote: > I have a PMPAY batch where the status is 'Receiving' these are configured to > post through to the GL. ...

extracting data from word to an excel spreadsheet
Thank You for any help. I am trying to find a way to automate the extraction of data from a form in word2000 to excel2000. The form is for HR at my company, we want to track the check boxes that are marked in employee review forms and bring totals into and excel spreadsheet. I know this is going to involve macros, but I cannot find any literature regarding vb in excel. I only find visual basic and from any of the material I found, Visual Basic and vb in excel are not compatible. Please help me and steer me into the correct direction. Thank You again!! Kevin R. ...

On POS (not RMS) Adjusting Time Clock Defaults to Today's Date #2
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Outlook task #2
I am running Outlook XP Pro and Windows 2000 and I'm wondering is there a way to arraign tasks by categories. Any help would be appreciated. Lawrence <anonymous@discussions.microsoft.com> wrote: > I am running Outlook XP Pro and Windows 2000 and I'm > wondering is there a way to arraign tasks by categories. > Any help would be appreciated. In the TaskPad, click View>By Category -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is the domain. I don't sp...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Sheets Skip Macro
Hello I have a lot of sheets in an excel workbook and a front summary sheet where i have a vlookup formula that i would like to copy across to the right however each column needs to change the formula to pick up the data in the different sheets. Does anyone know a macro that will help me so that each column copied across looks up in the next sheet. Eg. 5 sheets - Daniel, Mark, Byran, Hugh & Paul on the summary page i need it to go =VLOOKUP(ref,Daniel!F2:F17,col,false) then =VLOOKUP(ref,Mark!F2:F17,col,false) then =VLOOKUP(ref,Byran!F2:F17,col,false) then =VLOOKUP(ref,Hugh!F2:F17,c...

Beginer
hell friends, i got rid of earlier problem but i am facing one more new problem. Problem : How to disable the default buttons present in the tab dialog. i am attaching the code below. Can one tell me what mistake i have done? CTab1 tab1; CTab2 tab2; sheet.AddPage(&tab1); sheet.AddPage(&tab2); sheet.m_psh.dwFlags &= ~(PSH_HASHELP); //trying to disable help button sheet.DoModal(); i have searched in google and in msdn about this problem but i could not able to find the solution. please help Thank you in advance Murthy >How to disable the default buttons present i...

help with formula #2
Here is the answer of my question sent to Google on 2003- 07-15 00:41:33 PST : """ Tim wrote in message: > > I've got a workbook with many worksheets on it(about 60). On 2 columns > on a sheet in this workbook i have data and i need to export this data > to particular cells in other worksheets. For example on columns A1:A20 > and B1:B20 on a worksheet is my data and I want to export the contents > of cells A1B1 to cells A1B1 in worksheet1 then data from A2B2 to cells > A2B2 in worksheet2 and so on. > What is the easiest way to do this job? > ...

Removing Delegates #2
I'm trying to remove a delegate from Outlook 2003 and I get this "The delegate settings were not saved correctly. Unable to active send-on-behalf-of list. You do not have sufficient permission to perform this operation on this object." I have checked and no one has a permission to send-on-behalf-of list. Please help I have seen this happen when the GC (Global Catalog) server is not writable by you. This may not mean much to you, but it will to your Exchange Admin. Your Admin can either, point your system to a writable GC and/or Remove the hidden delegates using the Excha...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Command Button #2
How do I create a command button in a worksheet and assign a command t it using the control box tools, e.g., to sort a list of figures in sheet? Thank you, vija -- vijay2 ----------------------------------------------------------------------- vijay22's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3411 View this thread: http://www.excelforum.com/showthread.php?threadid=53885 The macro recorder is your friend. Just use tools>macros>record when you do your sort manually. Then, assign to a button or a shape. I do not recommend command buttons. -- Don...

but there is no macro.... #2
Thank you very much!! I've now got it sorted - I followed the link and found several little bits of code. Thank you Thank you Thank you Thank you! :) -- grrr ------------------------------------------------------------------------ grrr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15715 View this thread: http://www.excelforum.com/showthread.php?threadid=272394 ...

The "Open" statement #2
I'm struggling with the syntax of the open statement, the helpfil states it as -Open pathname For mode [Access access] [lock] As [#]filenumbe [Len=reclength]- I know this makes me look thick but I've tried various things that hav all failed, can someone give me an example of how this would look for file named "carrier" that I wanted to input to as the mode? Thank -- carl0s6 ----------------------------------------------------------------------- carl0s66's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1662 View this thread: http://www.excelf...