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

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
705
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi SandsB,

AGGREGATE should work for you.

Here's the DATA tab:

ABCJ
1NameCompanySalesDate
2Hannah RossEasy Partners$132,06217-May-19
3Bert JonesAltwood Bits$27,55808-May-19
4Bert JonesAltwood Bits$247,32008-May-19
5Hannah RossEasy Partners$117,37514-May-19
6Hannah RossEasy Partners$212,89117-May-19
7Hannah RossEasy Partners$339,26017-May-19
8Hannah RossEasy Partners$232,42117-May-19

<tbody>
</tbody>
DATA

...and here's the report tab:

EFGHIJK
1Find=Hannah Ross17-May-19
2Count=4
3NameCompanySalesDate
4Hannah RossEasy Partners$132,06217-May-19
5Hannah RossEasy Partners$212,89117-May-19
6Hannah RossEasy Partners$339,26017-May-19
7Hannah RossEasy Partners$232,42117-May-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
SandsB

Worksheet Formulas
CellFormula
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))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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.

EFGHIJK
1Find=Hannah Ross17-May-19
2Count=4
3NameCompanySalesDate
4Hannah RossEasy Partners$132,06217-May-19
5Hannah RossEasy Partners$212,89117-May-19
6Hannah RossEasy Partners$339,26017-May-19
7Hannah RossEasy Partners$232,42117-May-19

<tbody>
</tbody>
SandsB

Worksheet Formulas
CellFormula
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))))

<tbody>
</tbody>

<tbody>
</tbody>

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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top