none
Extract subset from data in one sheet to another sheet

    Question

  • Assume I have one set of data as following:

    Name, Age, Gender

    John,18,Male

    Alice,20, Female

    Tom, 21,Male

    ......

    And these data is from outside the Excel file. My purpose is to implement the following logic:

    1. Data descrbed above saved in sheet1.

    2. Subset of the data describe above saved in sheet2. Such as rows whose gender's value is Male - Extract all male rows from sheet1 to sheet2.

    3. Once data in sheet1 is changed, data in sheet2 is also changed.

     

    My dear friends, is there one solution for this?

    Thanks in advance!

     

    Wednesday, January 05, 2011 10:45 AM

Answers

  • Below is code that I've posted lost of times for people.  I even put in a test so autofilter doesn't give an eror when "Male" isn't found in the column.  The method you described in the webpage is exactly like I've always done my code in the past.

     

    Sub CopyMale()
    '
    '
    
    '
    With Sheets("Sheet1")
      'autofilter will give an error if nothing is found
      Set c = .Columns("C").Find(What:="Male", _
       LookIn:=xlValues, lookat:=xlWhole)
       
      If Not c Is Nothing Then
       LastRow = .Range("A" & Rows.Count).End(xlUp).Row
       
       .Columns("C").AutoFilter
       .Columns("C").AutoFilter _
         Field:=1, _
         Criteria1:="Male"
      
       Set SourceRows = _
         .Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible)
       
       'clear data from sheet 2
       Sheets("Sheet2").Cells.Clear
       SourceRows.Copy _
         Destination:=Sheets("Sheet2").Rows(1)
      
       .Columns("C").AutoFilter
      End If
    End With
    End Sub
    
    

    jdweng
    • Proposed as answer by Bruce Song Wednesday, January 12, 2011 7:48 AM
    • Marked as answer by Bruce Song Thursday, January 13, 2011 2:51 AM
    Thursday, January 06, 2011 3:55 AM

All replies

  • the reason why nobody is really answering you request is that the 3rd requirment can be complicated because of the following

    1. You can change existing data
    2. You can add new rows to the worksheet
    3. You can delete rows from the worksheet

    The adding and deleting rows is the issue.  I would recommend manually running a macro after all the updates are made rather than it being automatic.  When the macro is run sheet 2 would be cleared before copying the datta from sheet 1.

    A macro can do the following.  Which options do you need

    1. Dialog box is display to let user select file
    2. You data is comma seperated.  So is all your data in column A and the macro needs to run Text-To-Column to put the data into columns A:C.

    Noramally in these type macros I would use Autofilter to get the Male exmployees and then use SpecialCells method to copy the visible rows from the autofilter from sheet 1 to sheet 2.


    jdweng
    Wednesday, January 05, 2011 4:34 PM
  •  

    Hi Joel,

    Thanks for your reply!

    My data is already in Excel and no need to run Text-To-Column. It's just to show the data structure by seperated comma.

    My purpose is just automatically extract subset from one big set based on one criteria. Just like the scenario described in http://en.allexperts.com/q/Excel-1059/Extract-Data-sheet.htm. However, my aim is to get sub-rows.

    I know its not easy to reach this point. However, it's very useful in practice.

     

     


    Bruce Wen (GuangMing) ----------------------------------------------------------------------------------------------------------------- "Undertake not what you cannot perform, but be careful to keep your promise - George Washington" -----------------------------------------------------------------------------------------------------------------
    Thursday, January 06, 2011 2:57 AM
  • Below is code that I've posted lost of times for people.  I even put in a test so autofilter doesn't give an eror when "Male" isn't found in the column.  The method you described in the webpage is exactly like I've always done my code in the past.

     

    Sub CopyMale()
    '
    '
    
    '
    With Sheets("Sheet1")
      'autofilter will give an error if nothing is found
      Set c = .Columns("C").Find(What:="Male", _
       LookIn:=xlValues, lookat:=xlWhole)
       
      If Not c Is Nothing Then
       LastRow = .Range("A" & Rows.Count).End(xlUp).Row
       
       .Columns("C").AutoFilter
       .Columns("C").AutoFilter _
         Field:=1, _
         Criteria1:="Male"
      
       Set SourceRows = _
         .Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible)
       
       'clear data from sheet 2
       Sheets("Sheet2").Cells.Clear
       SourceRows.Copy _
         Destination:=Sheets("Sheet2").Rows(1)
      
       .Columns("C").AutoFilter
      End If
    End With
    End Sub
    
    

    jdweng
    • Proposed as answer by Bruce Song Wednesday, January 12, 2011 7:48 AM
    • Marked as answer by Bruce Song Thursday, January 13, 2011 2:51 AM
    Thursday, January 06, 2011 3:55 AM
  • Hi Jdweng,

    Thanks a lot for your code! It's very helpful for me although it does not reach the end point. Thanks again!


    Bruce Wen (GuangMing) ----------------------------------------------------------------------------------------------------------------- "Undertake not what you cannot perform, but be careful to keep your promise - George Washington" -----------------------------------------------------------------------------------------------------------------
    Thursday, January 06, 2011 7:11 AM