Results 1 to 5 of 5

Thread: Need a formula to pull rows from another tab based on 2 cells

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    Chicago
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need a formula to pull rows from another tab based on 2 cells

    My DATA tab lists Name (Column A), Date (Column K), and about 10 other columns of info. Row 1 is a header row. In my Report tab, I use Data Validation to display a dropdown for the Name and another for the Date - both in Row 1. Row 2 is blank, Row 3 is the same header row as in the DATA tab. What I'd like to appear under this are all of the rows from the DATA tab that match both the Name and Date selected in those two drop downs. This would allow me to view all the rows of data for Bob in August and the change the dropdowns to see all the data for Sue for May, etc. The number of rows that match these criteria vary - One employee may have 1500 rows for August and another employee may have 15000 rows for the same month.
    Thanks for the help

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need a formula to pull rows from another tab based on 2 cells

    Hi SandsB,

    AGGREGATE should work for you.

    Here's the DATA tab:

    A B C J
    1 Name Company Sales Date
    2 Hannah Ross Easy Partners $132,062 17-May-19
    3 Bert Jones Altwood Bits $27,558 08-May-19
    4 Bert Jones Altwood Bits $247,320 08-May-19
    5 Hannah Ross Easy Partners $117,375 14-May-19
    6 Hannah Ross Easy Partners $212,891 17-May-19
    7 Hannah Ross Easy Partners $339,260 17-May-19
    8 Hannah Ross Easy Partners $232,421 17-May-19
    DATA

    ...and here's the report tab:

    E F G H I J K
    1 Find= Hannah Ross 17-May-19
    2 Count= 4
    3 Name Company Sales Date
    4 Hannah Ross Easy Partners $132,062 17-May-19
    5 Hannah Ross Easy Partners $212,891 17-May-19
    6 Hannah Ross Easy Partners $339,260 17-May-19
    7 Hannah Ross Easy Partners $232,421 17-May-19
    SandsB

    Worksheet Formulas
    Cell Formula
    F2 =COUNTIFS(DATA!A:A,$F$1,DATA!J:J,$G$1)
    H4
    to
    H15003
    =IF(ROWS($H$4:$H4)>$F$2,"",INDEX(DATA!$A$2:$A$15001,AGGREGATE(15,6,ROW(DATA!$A$2:$A$15001)-1/((DATA!$A$2:$A$15001=$F$1)*(DATA!$J$2:$J$15001=$G$1)),ROWS(H$4:H4))))
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular
    Join Date
    Feb 2007
    Location
    Chicago
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need a formula to pull rows from another tab based on 2 cells

    I can see this will be something I use on a lot of reports going forward. I don't understand the aggregate function yet. Still scratching my head. I'll keep playing with it.

    I don't see how you get data in columns I, J, K. Can you please reply with the formulas that go in I2 and J2? And to be sure I've got this partially right, H5?

    Thank you in advance.
    Thanks for the help

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need a formula to pull rows from another tab based on 2 cells

    Sorry, I trimmed too much off the html table. The only difference between H, I, J and K formulae is which column the INDEX pulls the data from.

    E F G H I J K
    1 Find= Hannah Ross 17-May-19
    2 Count= 4
    3 Name Company Sales Date
    4 Hannah Ross Easy Partners $132,062 17-May-19
    5 Hannah Ross Easy Partners $212,891 17-May-19
    6 Hannah Ross Easy Partners $339,260 17-May-19
    7 Hannah Ross Easy Partners $232,421 17-May-19
    SandsB

    Worksheet Formulas
    Cell Formula
    F2 =COUNTIFS(DATA!A:A,$F$1,DATA!J:J,$G$1)
    H4 =IF(ROWS($H$4:$H4)>$F$2,"",INDEX(DATA!$A$2:$A$15001,AGGREGATE(15,6,ROW(DATA!$A$2:$A$15001)-1/((DATA!$A$2:$A$15001=$F$1)*(DATA!$J$2:$J$15001=$G$1)),ROWS(H$4:H4))))
    I4 =IF(ROWS($H$4:$H4)>$F$2,"",INDEX(DATA!$B$2:$B$15001,AGGREGATE(15,6,ROW(DATA!$A$2:$A$15001)-1/((DATA!$A$2:$A$15001=$F$1)*(DATA!$J$2:$J$15001=$G$1)),ROWS(H$4:H4))))
    J4 =IF(ROWS($H$4:$H4)>$F$2,"",INDEX(DATA!$C$2:$C$15001,AGGREGATE(15,6,ROW(DATA!$A$2:$A$15001)-1/((DATA!$A$2:$A$15001=$F$1)*(DATA!$J$2:$J$15001=$G$1)),ROWS(H$4:H4))))
    K4 =IF(ROWS($H$4:$H4)>$F$2,"",INDEX(DATA!$J$2:$J$15001,AGGREGATE(15,6,ROW(DATA!$A$2:$A$15001)-1/((DATA!$A$2:$A$15001=$F$1)*(DATA!$J$2:$J$15001=$G$1)),ROWS(H$4:H4))))

    Quick explanation:

    The IF ROWS part checks if rhe number of rows displayed so far has matched the COUNTIF, in which case there's no more "Hannah Ross" to report so show null "".

    The INDEX retrieves the data from the specified column for the row number returned by the AGGREGATE.

    The AGGREGATE uses the SMALL option (15) and ignores error (6) when it looks down all row numbers for 2 to 15001 (-1 to account for the header row). Then divide (/) that row number by a check if column A = F1 (the Hannah Ross name) multiplied by a check of column J equaling the search date (in this case 17-May-19).
    If either the A column isn't F1 or J column isn't G1 then they give a zero so multiplying (*) them together will always give a zero. Only if both are true will you get a 1.
    The row number divided by zero gives a #DIV0 error which the 6 option of AGGREGATE ignores. The row number divided by 1 gives the row number which the INDEX can then use to return the result.

    The last ROWS function just increments the counter by 1 for each row so the AGGREGATE with SMALL returns the 1st smallest row, then the second smallest row, etc.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #5
    Board Regular
    Join Date
    Feb 2007
    Location
    Chicago
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need a formula to pull rows from another tab based on 2 cells

    Great explanation - thank you
    Thanks for the help

Some videos you may like

User Tag List

Tags for this Thread

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
  •