+ Reply to Thread
Results 1 to 8 of 8

Retrieving complete rows of data based on 2 criteria

  1. #1
    Registered User
    Join Date
    09-17-2015
    Location
    Hungary
    MS-Off Ver
    2010
    Posts
    6

    Retrieving complete rows of data based on 2 criteria

    I have a table where column „A” is the name of a given project; column „B” is the ID of a participant working on the project, and Column „C” signifies the nationality of the participant.
    I am analyzing the composition of project participants, and I have to list all the projects along with all the participants that are carried out through the collaboration of 2 selected countries.
    So I need a function that
    • Step1. - Based on my variables (nationality 1 and nationality 2) finds all those projects where these two countries are collaborating
    • Step 2. –Retrieves all the participants of the projects where nationality 1 and nationality 2 are collaborating
    • Step 3. – lists all the rows that contain the retrieved participant data

    My solution involved filters and Vlookup functions – although it yielded results, it was really tiresome.
    Is there a way to do this efficiently?
    I also attach an excel spreadsheet to clarify what I mean.

    Thanks for the help in advance.
    Attached Files Attached Files

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

    Re: Retrieving complete rows of data based on 2 criteria

    with a pivot table.
    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
    09-17-2015
    Location
    Hungary
    MS-Off Ver
    2010
    Posts
    6

    Re: Retrieving complete rows of data based on 2 criteria

    Thanks very much! But this is not what I had in mind.
    I also tried pivot tables, but with that, I cannot see who are the other participants of the relevant projects. What I need is a function that returns all elements of a group, if 2 given elements are present. E.g.: I am looking for projects where German and Austrian partners are collaborating. The function should find all these projects, and should list all the other participants - not just the German and Austrian- along with all other data.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Retrieving complete rows of data based on 2 criteria

    This solution uses five helper columns, which may be hidden for aesthetic purposes. The first two of which have formulas similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The last three have formulas similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The table is populated with formulas similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The country selections are made using the data validation in J5 and J6.
    Let me know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    09-17-2015
    Location
    Hungary
    MS-Off Ver
    2010
    Posts
    6

    Re: Retrieving complete rows of data based on 2 criteria

    Wow! Thanks very much!
    I think I can follow the logic until this formula:

    =IFERROR(INDEX(F$2:F$30,AGGREGATE(15,6,(ROW(F$2:F$30))/(F$2:F$30<>""),ROW(A1))),"")

    I really don't want to steal your time, but could you explain in detail what this function does?
    Also, I tested your solution with different countries and found out that participants of the second country will not be included in the final table. Why is that happening?
    Anyway, thank you very much for your work, I would never have figured out that myself.
    Thanks again!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Retrieving complete rows of data based on 2 criteria

    Try the final table now, the formula has been modified to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To start with the inner function, AGGREGATE, the arguments 15 (small) and 6 (ignore errors) find the kth, as determined by ROW(), smallest row, from ROW(F$1:F$30), that isn't blank, determined by (F$2:F$30<>""), and doesn't have an error in column F. INDEX will then return the value that it finds in that row of column F. IFERROR will show a blank instead of an error value, #NUM in this case, in the event that there are no more values to be put in the table by the formula. To see what is happening run evaluate formula cell I21 and again on cell I22.
    Let me know if you have any questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-17-2015
    Location
    Hungary
    MS-Off Ver
    2010
    Posts
    6

    Re: Retrieving complete rows of data based on 2 criteria

    Totally awesome!
    Thank you very much, it works perfectly.
    Thanks for the detailed description as well.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Retrieving complete rows of data based on 2 criteria

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ 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. Copy complete rows matching criteria to another sheet automatically
    By abdulahadzafar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2014, 12:09 PM
  2. Retrieving Data that meets a certian criteria
    By Otaishtf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-26-2013, 03:43 AM
  3. If Statement - to copy complete row based a criteria of one cell in row
    By chloesnowling in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2013, 06:59 AM
  4. [SOLVED] Insert rows based on a number and copy complete row
    By prashantsd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2012, 10:41 AM
  5. Replies: 7
    Last Post: 08-26-2011, 06:00 PM
  6. Replies: 0
    Last Post: 08-25-2011, 01:59 PM
  7. Retrieving Sale Price Based on Item and Color Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 03:01 AM

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