Results 1 to 9 of 9

Thread: Help with extracting multiple rows from 5 sheets into one sheet based on criteria

  1. #1

    Help with extracting multiple rows from 5 sheets into one sheet based on criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello!
    I am a novice with Excel and need to extract multiple rows from 5 different sheets to one sheet based on certain criteria. Additional data is being entered into the 5 sheets regularly, so I need the solution to auto-populate(dynamic?). I am using Excel 2013, on Windows 7. I have never used anything other than formulas in my spreadsheets.

    Here are some more details:
    On the 5 sheets are the production details for the individuals on our sales team. The 5 sheets are broken down by the different segments of our business. The landing sheets for the funneled data are labeled for each salesperson. I do not need to tally any of the data, just copy it.

    For example:

    Cars

    John 1/12 GL $2300 No Red
    Dave 1/12 GL $2500 No Green
    John 1/12 GL $2300 No Red
    Mary 1/12 GL $2700 No Blue
    John 1/12 GL $2300 No Yellow
    John 1/12 GL $9300 No Red

    Boats

    John 1/12 GL $2200 No Red
    Mike 1/12 GL $2300 No Yellow
    Mike 1/12 PL $8300 No Green
    Bill 1/12 WL $3300 No Yellow
    John 1/12 GL $2300 No Red

    Bikes

    Steve 1/12 GL $2300 No Red
    Tom 1/12 YL $2300 No Green
    Mary 1/12 GL $2300 No Red
    John 1/12 KL $2300 No Yellow
    Mike 1/12 ML $2300 No Red


    I need to extract all of the listings from John into John's sheet. Cars, Boats, and Bikes are all different sheets. Each sheet's first column is the salesperson's name. Each sheet has the same column headers and the same number of columns.

    Please let me know what other information you may need.

    Thank you to whomever can help me with this!

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by JMSisto View Post
    Please let me know what other information you may need.
    Can You attach your example file?
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Quote Originally Posted by navic View Post
    Can You attach your example file?

    NEW BUSINESS .xlsx

    I need to funnel all rows based on the source, into the source's sheet, from all five categories (commercial, personal, bonds, health, and main street)

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by JMSisto View Post
    I need to funnel all rows .....
    First, you have a mistake in writing the person's name. There is a blank character (space) at the end of the name. This empty character should be removed. (You can use TRIM function)

    I've tried to solve your problem using several formulas but not successfully.
    This is one of the formulas that I did not by the end I managed to finish.
    I would also like when someone completed this formula below.
    Code:
    =IFERROR(INDEX(tbl_1;SMALL(IF(("criteria"=$A$2:$A$750);ROW($A$2:$A$750)-MIN(ROW($A$2:$A$750))+1;"");ROW(A1));2);INDEX(tbl_2;SMALL(IF(("criteria"=Sheet2!$A$2:$A$750);ROW(Sheet2!$A$2:$A$750)-MIN(ROW(Sheet2!$A$2:$A$750))+1;"");ROW(A1)-SUM(--("criteria"=$A$2:$A$750)));2))
    I decided on a different way with extra worksheet. The file is large because data is duplicated on HELPER worksheet.

    I believe that this can be solved using VBA but that's for the experts in Excel
    Look at my example attached (I had to compress the file because it is too large).
    Attached Files Attached Files
    Last edited by navic; 2015-07-18 at 04:34 PM.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Excel 2010 with free PowerQuery Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas used, for sure.
    Dynamic.
    http://www.mediafire.com/view/6iy1pzxvod7myt3/07_18_15.xlsx


  6. #6

    Cool

    Thank you this is awesome Herbds7, but what steps do I need to do to make new sheets for the rest of the producers?

  7. #7
    This is great thank you! What steps do I need to take to add sheets for the remaining producers?

  8. #8
    Read the book
    "Power Query for Power BI and Excel"
    by Chris Webb,
    and duplicate the now clear steps already listed in the Query Pane.

  9. #9
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    I see what you are trying to do with your current setup. If you would consider another setup, you would be able to use a simple pivot table to pull all info quite easily. I can play with your NEW BUSINESS spreadsheet and amend it slightly to show you what I mean. Of course, if you need to stay with your current format, my solution won't work.

Posting Permissions

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