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

SandsB

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
731
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi SandsB,

AGGREGATE should work for you.

Here's the DATA tab:

ABCJ
NameCompany
Hannah RossEasy Partners
Bert JonesAltwood Bits
Bert JonesAltwood Bits
Hannah RossEasy Partners
Hannah RossEasy Partners
Hannah RossEasy Partners
Hannah RossEasy Partners

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Sales[/TD]
[TD="align: center"]Date[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]$132,062[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]$27,558[/TD]
[TD="align: center"]08-May-19[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]$247,320[/TD]
[TD="align: center"]08-May-19[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]$117,375[/TD]
[TD="align: center"]14-May-19[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]$212,891[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]$339,260[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]$232,421[/TD]
[TD="align: center"]17-May-19[/TD]

</tbody>
DATA​

...and here's the report tab:

EFGHIJK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Find=[/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]17-May-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Count=[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]Date[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$132,062[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$212,891[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$339,260[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$232,421[/TD]
[TD="align: center"]17-May-19[/TD]

</tbody>
SandsB​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIFS(DATA!A:A,$F$1,DATA!J:J,$G$1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4
to
H15003
[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Find=[/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]17-May-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Count=[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Company[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]Date[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$132,062[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$212,891[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$339,260[/TD]
[TD="align: center"]17-May-19[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Hannah Ross[/TD]
[TD="align: center"]Easy Partners[/TD]
[TD="align: center"]$232,421[/TD]
[TD="align: center"]17-May-19[/TD]

</tbody>
SandsB​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIFS(DATA!A:A,$F$1,DATA!J:J,$G$1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K4[/TH]
[TD="align: left"]=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))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
You must log in or register to reply here.

Similar threads

W
  • Question Question
Formatting raw data with extra rows automatically added in-between records
Replies
1
Views
57
Peter_SSs
Peter_SSs
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
172
datatronics505
datatronics505
A
  • Question Question
Auto populate columns in one tab based on entries in another
Replies
6
Views
105
dermie_72
D
N
  • Question Question
Spitting every 2000 rows of data to new workbook based on duplicate value
Replies
1
Views
83
Skyybot
Skyybot
Y
  • Question Question
VBA Code to activate the tab and and transfer data
Replies
1
Views
240
Joe4
Joe4

Forum statistics

Threads
1,223,949
Messages
6,175,581
Members
452,653
Latest member
craigje92

Share this page

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