Question:

I have a dataset that looks like the example below. I want to pull a list of the last ten transactions, ordered by Transaction Date, while capturing all columns of information in the original dataset; and I want to do this only IF the Salesman is Jimmy.

I'm stumped. Any ideas how to do this?

Example dataset below:

Customer Name Customer Group Salesman Transaction Date Sam 1 Jimmy 3/21/2015 Jill 2 Johnny 3/21/2015 Scott 3 Joanny 3/21/2015 Sean 4 Slippy 3/24/2015 Dave 5 Slappy 3/25/2015 Amber 4 Slummy 3/26/2015 Shawn 3 Jimmy 3/24/2015 Matt 2 Johnny 3/26/2015 Matthew 4 Joanny 3/24/2015 Mark 3 Slippy 3/21/2015 Luke 2 Slappy 3/26/2015 John 1 Slummy 3/26/2015 Jonathan 5 Jimmy 3/24/2015 Zach 3 Johnny 3/26/2015 Asher 2 Joanny 3/21/2015

Desired output is of last 10 transactions ordered by Forecast Transaction Date

Customer Name Customer Group Salesman Forecast Transaction Date

Update:

I'm using this formula, mimicking Scott's but its erroring out.

=iferror(index(All_Incremental_MDA_JV_Forecast__2[[#Headers],[Opportunity Name]]:index(All_Incremental_MDA_JV_Forecast__2[Opportunity Name],match(1E+99,All_Incremental_MDA_JV_Forecast__2[Forecast Close Date])),aggregate(15,6,row(All_Incremental_MDA_JV_Forecast__2[[#Headers],[Forecast Close Date]]:index(All_Incremental_MDA_JV_Forecast__2[Forecast Close Date],match(1E+99,All_Incremental_MDA_JV_Forecast__2[Forecast Close Date]))-3)/((All_Incremental_MDA_JV_Forecast__2[[#Headers],[Pegasus Stage]]:index(All_Incremental_MDA_JV_Forecast__2[Pegasus Stage],match(1E+99,All_Incremental_MDA_JV_Forecast__2[Forecast Close Date]))="Closed Affirmative - Contract Signed")*(All_Incremental_MDA_JV_Forecast__2[[#Headers],[Forecast Close Date]]:index(All_Incremental_MDA_JV_Forecast__2[Forecast Close Date],match(1E+99,All_Incremental_MDA_JV_Forecast__2[Forecast Close Date]))='MDA-JV'!V10)),countif('MDA-JV'!$V$10:'MDA-JV'!$V10,'MDA-JV'!V10))),"")

Mine takes a slightly different tack than @Jeeped. Though I did steal the INDEX/MATCH that allows the ranges to grow and contract to the correct size automatically.

Putting the name of the Salesperson desired in L1 and starting with the Transaction Date. To get the Transaction dates in order:

=IFERROR(AGGREGATE(14,6,$D$2:INDEX(D:D,MATCH(1E+99,D:D))/($C$2:INDEX(C:C,MATCH(1E+99,D:D))=$L$1),ROW(1:1)),"")

` And copy down 10 rows.

Then then customer name refers to the Transaction Date which I put in Column I:

=IFERROR(INDEX($A$1:INDEX(A:A,MATCH(1E+99,D:D)),AGGREGATE(15,6,ROW($D$1:INDEX(D:D,MATCH(1E+99,D:D)))/(($C$1:INDEX(C:C,MATCH(1E+99,D:D))=$L$1)*($D$1:INDEX(D:D,MATCH(1E+99,D:D))=I2)),COUNTIF($I$2:$I2,I2))),"")

The Salesman column is a simple refernce to L1.

To get the Group I used a simple SUMIFS():

=IF(I2<>"",SUMIFS(B:B,A:A,F2,C:C,H2,D:D,I2),"")

<a href="https://i.stack.imgur.com/lyqjW.png" rel="nofollow"><img alt="![enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/lyqjW.png" data-original="https://i.stack.imgur.com/lyqjW.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

This will order the entries by date starting with the newest.

The <a href="https://support.office.com/en-us/article/AGGREGATE-function-43B9278E-6AA7-4F17-92B6-E19993FA26DF" rel="nofollow">AGGREGATE function</a> can return successive matching row numbers to the <a href="https://support.office.com/en-us/article/index-function-0ee99cef-a811-4762-8cfb-a222dd31368a" rel="nofollow">INDEX function</a>.

With <em>jimmy</em> in H2, put this standard formula in F2

=INDEX(A:A, AGGREGATE(15, 6, ROW($A$1:INDEX($A:$A, MATCH(1E+99,$D:$D )))/($C$1:INDEX($C:$C, MATCH(1E+99,$D:$D ))=$H2), ROW(1:1)))

Copy to G2 and I2 then fill down as necessary.

  <a href="https://i.stack.imgur.com/qSOTO.png" rel="nofollow"><img alt="jimmy_orders" class="b-lazy" data-src="https://i.stack.imgur.com/qSOTO.png" data-original="https://i.stack.imgur.com/qSOTO.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

Use the <a href="https://support.office.com/en-us/article/IFERROR-function-F59BACDC-78BD-4924-91DF-A869D0B08CD5" rel="nofollow">IFERROR function</a> to avoid displaying #NUM! errors when you run out of matches to return.

This is based on the sort order of the original data. If you want to reverse the sort order change the 15 (AGGREGATE's <a href="https://support.office.com/en-us/article/SMALL-function-4050a82e-659a-4ef6-a970-c963a6f69c29" rel="nofollow">SMALL</a> sub-function) to 14 (AGGREGATE's <a href="https://support.office.com/en-in/article/large-function-3688ad91-0b0c-43db-8ef8-825a5e5871db" rel="nofollow">LARGE</a> sub-function). If the original data is unsorted and you require sorted results, then you need to first get the smallest date using similar pseudo-SMALLIF function and then use that in a multiple criteria to retrieve the other matching columns, adjusting for duplicate dates if necessary.

<strong>Addendum for <a href="https://msdn.microsoft.com/en-us/library/office/aa174247.aspx" rel="nofollow">ListObject</a> tables</strong>

To start off, rename your table temporarily while you develop the formula. While its current name (e.g. <em>All_Incremental_MDA_JV_Forecast__2</em>) may be necessary to identify it for other purposes, all it does now is obfuscate the formula; essentially hiding small errors that might otherwise be immediately apparent. To this end, I have renamed your table <strong>aimf2</strong>. As soon as formula development is complete, it can be renamed back to its original and all references will be immediately updated.

As per the following image the formulas in I4:L4 are,

=INDEX(aimf2[Customer Name], AGGREGATE(15, 6, (ROW(aimf2[Customer Name])-ROW(aimf2[#Headers]))/((aimf2[Salesman]=$G$3)*(aimf2[Transaction Date]=L4)), COUNTIF(L$4:L4, L4))) =INDEX(aimf2[Customer Group], AGGREGATE(15, 6, (ROW(aimf2[Customer Name])-ROW(aimf2[#Headers]))/((aimf2[Salesman]=$G$3)*(aimf2[Transaction Date]=L4)), COUNTIF(L$4:L4, L4))) =PROPER($G$3) =AGGREGATE(14, 6, aimf2[Transaction Date]/(aimf2[Salesman]=$G$3), ROW(1:1))

Add error control if desired and fill down for ten rows.

  <a href="https://i.stack.imgur.com/S4rhk.png" rel="nofollow"><img alt="jimmy_sales_two" class="b-lazy" data-src="https://i.stack.imgur.com/S4rhk.png" data-original="https://i.stack.imgur.com/S4rhk.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a><br />               ^{<a href="https://dl.dropboxusercontent.com/u/100009401/Using%20If%20Large%20Statement%20and%20Populating%20Remainder%20of%20Table%20in%20Excel.xlsx" rel="nofollow">Sample worksheet</a> with ListObject tables}