+ Reply to Thread
Results 1 to 4 of 4

Returning multiple row based on criteria

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2007
    Posts
    2

    Returning multiple row based on criteria

    Hi,

    New on here and I'm looking for help if possible please. I have two worksheets and I want to insert multiple rows from one worksheet to another if criteria are met.

    Example below. I have 8 columns in worksheets 'RATINGS' and I only want to return the rows to worksheet 'SUMMARY' where the 'RatingTotal' is greater than 70.

    Date Product1 Product2 Product3 Rating1 Rating2 Rating3 RatingTotal
    30/09/2013 Ball Bell Belt 10.00 10.00 5.00 25.00
    01/10/2013 Ball2 Bell2 Belt2 10.00 15.00 10.00 35.00
    02/10/2013 Ball3 Bell3 Belt3 35.00 20.00 10.00 65.00
    03/10/2013 Ball4 Bell4 Belt4 25.00 25.00 25.00 75.00
    03/10/2013 Ball5 Bell5 Belt5 20.00 30.00 35.00 85.00

    I need a formula that will work over a range but I should know if the no. of rows in worksheet 'RATINGS' if this helps. It's nearly like cut & paste but I want it automated so in the above example the only rows that should be returned to worksheet 'SUMMARY' are:

    03/10/2013 Ball4 Bell4 Belt4 25.00 25.00 25.00 75.00
    03/10/2013 Ball5 Bell5 Belt5 20.00 30.00 35.00 85.00

    Hope this makes sense and if there is anybody out there that can suggest a solution I'd be grateful.

    Thanks

    Marty

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Returning multiple row based on criteria

    This is what the Autofilter is for.

    1) Click on the header row
    2) Select Data > Filter to turn on the filter arrows
    3) Select the RatingTotal drop arrow and apply a CUSTOM filter for values greater than or equal to 70.
    4) Copy all visible rows to Summary
    5) Turn off the filter.

    This will work quickly with dataset of any size.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    Belfast
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Returning multiple row based on criteria

    Thanks, I know the Data Filter will do this but i am looking for an autmated solution so I can scale up as I will be adding multiple worksheets in the book. So I want it to do the job of essential a manual cut and paste but I want it automated.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Returning multiple row based on criteria

    Ah, then perhaps you posted in the wrong part of the forum if you're looking for automation, then the VBA Programming forum would be more appropriate? Shall I move this for you?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto deletion of worksheets based on multiple criteria, and automatically returning values
    By iamrickdeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2013, 01:52 AM
  2. Replies: 3
    Last Post: 09-06-2012, 09:07 AM
  3. Returning multiple rows of data based on certain criteria
    By ghurhu in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-19-2012, 03:54 AM
  4. Returning a lookup value based on multiple criteria.
    By ahunter488 in forum Excel General
    Replies: 3
    Last Post: 06-09-2011, 01:55 AM
  5. returning entire row based on single or multiple criteria
    By cdevericks in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2008, 09:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1