Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Formulas

  1. #1
    Guest

    Default

    We have a master spreadsheet that we download from a call center that lists all people who called, addresses, telephone number and comments. We sort by state. I would like to make Excel pull all the people from Vermont and copy to another page, all people from New Jersey and copy onto a separate page, etc ... in one shot. Any suggestions?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This can be accomplished using an Advanced AutoFilter in a recorded macro.

  3. #3
    Board Regular hennahairgel's Avatar
    Join Date
    Feb 2002
    Location
    Bristol, England
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The autofilter function is very useful. However, if you store the address as a single cell value be slightly more cunning and filtet on a containing ie for vermont
    Selection.AutoFilter Field:=1, Criteria1:="=vermont", Operator:=xlAnd
    you can then paste and copy in ther normal way. if you send me a few lines of the data i can write the whole thing for you for free.
    Just tell me what sheets you want the data to go to, and if the sheets exist.
    ciao,
    Henry

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copy/Paste is a very inefficent way of transferring large amounts of data. As I was suggesting above this can be accomplished with a single VBA statement...

    Range("Sheet1!A1:C5").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Sheet1!F1:F2"), CopyToRange:=Range("Sheet2!A1:C1"), Unique:=False

    Here, a 3 field, 5 row data list is filtered on a single column criteria (F1:F2) and the results are "sent" directly to Sheet2.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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