+ Reply to Thread
Results 1 to 15 of 15

Extract data based on multiple criteria

  1. #1
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Extract data based on multiple criteria

    Hello,

    I was able to come up with a formula based on one criteria, but found it's more challenging to extract the data based on multiple criteria. What I'm looking to do is extract the data from the 'data' tab to the 'development dashboard' based on the construction status and year. The top 5 properties needs to be extracted in descending order for a construction status of "completed" and year of "2016". Thank you so much!!

    Extract data based on two criteria in order.xlsx

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract data based on multiple criteria

    with a pivot table.

    see the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    Thanks for helping out. Unfortunately, we can't use a pivot table for this project. It has to be from a formula. I'm thinking of perhaps adding a column and using true/false in the data section... I've just been swamped and haven't been able to do it yet. If you could think of anything in terms of a formula - I would be extremely grateful!!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract data based on multiple criteria

    Unfortunately, we can't use a pivot table for this project.
    I am always interested why?


    Your answers in sheet 2 are the expected results, since I got other anwers?

  5. #5
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    Yes - you definitely came up with the expected results... it's just the data is on a printable document and the people that use it will be updating the data frequently. They also have a hard time with pivot tables.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract data based on multiple criteria

    They also have a hard time with pivot tables.
    Then it is time to learn the power of pivot table.

    Pivot table is expanded every new version in Excel since 2003, so MS suggest this is the way to go.

  7. #7
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    I totally agree!!! I feel comfortable with pivot tables, just can't use it for this. Thanks so much.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract data based on multiple criteria

    I feel comfortable with pivot tables, just can't use it for this.
    I am always interested why (again)?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract data based on multiple criteria

    In G26

    =LARGE(IF((Table1[Construction status]="Completed")*(Table1[Year built]=2016),Table1[RBA SF],0),ROWS($1:1))

    Enter with Ctrl+Shift+Enter

    copy down

    In A26

    =INDEX(Table1[Address],MATCH($G26,Table1[RBA SF],0))

    Copy down

    Change "Address" to other column entries ("Submarket", "Owner" etc)

  10. #10
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    Thank you so much, John! Since the index/match is looking up the largest value based on the RBA... what happens if there are two values that are identical? Wouldn't it duplicate the first row in the data tab?

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract data based on multiple criteria

    Yes .. it would. Can column M in Data be used as a helper column or any available column?

  12. #12
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    yes - we can include a column in the data tab, then hide it. That would work

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract data based on multiple criteria

    In a Hidden column (I used Column4 for demo)

    =$L6+(ROW()*10^-5)

    and copy down,

    in G26

    =LARGE(IF((Table1[Construction status]="Completed")*(Table1[Year built]=2016),Table1[Column4],0),ROWS($1:1))

    Note: data will be that of the hidden e,g. 147000.00053. If you use this is in a calculation elsewhere you should ROUND

    in A26

    =INDEX(Table1[Address],MATCH($G26,Table1[Column4],0))

  14. #14
    Registered User
    Join Date
    08-20-2014
    Location
    San Mateo, CA
    MS-Off Ver
    2010
    Posts
    86

    Re: Extract data based on multiple criteria

    That was absolutely perfect. Thank you so much!!

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Extract data based on multiple criteria

    Thank you very much for the feedback and rep.

    Could also mark your duplicate post () as solved to avoid Dflak (or anyone else) addressing a solved problem.

+ 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. Replies: 0
    Last Post: 05-02-2016, 07:59 AM
  2. How to extract data based on multiple criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-11-2015, 07:56 PM
  3. Extract based on multiple criteria
    By whatever61 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-22-2015, 03:01 PM
  4. Replies: 3
    Last Post: 12-18-2014, 10:27 AM
  5. [SOLVED] Extract data based on multiple substring criteria
    By jspharriola in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-10-2013, 01:50 AM
  6. Extract Data Based on Multiple Criteria
    By trevaaaaaaa in forum Excel General
    Replies: 2
    Last Post: 03-14-2012, 11:42 AM
  7. Replies: 2
    Last Post: 03-23-2011, 06:19 PM

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