84675

Using If Large Statement and Populating Remainder of Table in Excel

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))),"")

Answer1:

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.

Answer2:

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 />               <sup><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</sup>

Recommend

  • QuickPay node.js REST api
  • System.Net.Ping NuGet Installation error
  • MSDeploy doesn't deploy to remote server using MSBuild and Visual Studio 2010
  • how to assign colour to subset of variables ggplot2
  • Using If Large Statement and Populating Remainder of Table in Excel
  • what the difference between qs and querystring
  • Cannot run Cordova Applications using Visual Studio 2013
  • Why does my javascript getter/setter require underscores?
  • Call relevant js library on click of each menu's elements before jquery load function
  • How to sync keychain over iCloud
  • Error when building AngularJSToDo hybrid app in Visual Studio
  • difficulty modifying two dimensional ruby array
  • Does performSelectorInBackground spawn new thread for each call?
  • Whitelist directories with .gitignore
  • VBA Application Defined Error
  • Extracting the two smallest values corresponding to the unique-to-the-column ID
  • LLVM Error When Using a Pass from Another Pass
  • Polymer: get the value of paper-input inside a paper-dialog after paper-button press “ok”
  • How to pass encrypted data via browser (HTML5) session variable
  • Excel VBA-Duplicates run with button/add location
  • ASP.NET Multiple Themes in One Site
  • Typescript compile on save not working in cordova .jsproject - 2015
  • How can I speed up this row-by-row operation in data.table
  • how to pivot complex dataframe
  • IBM DevOps Pipeline: How to Access Artifacts from Previous Job?
  • How to include Web reference endpoint configuration in another project
  • PhoneGap : How to upload APK files on Google Play Store
  • Einsum optimize fails for basic operation
  • How to update powerpivot pivot table filter via cell reference?
  • VBScript to check for open process by user
  • Unable to run SDL program in Eclipse but able to do so in Windows Explorer
  • What version of Java should I use with Cassandra 2.0?
  • Upper limits for fibonnacci
  • Parenthesis() and SQL Query Performance
  • How to display a tooltip according to mouse position? - JavaFX
  • C# fibonacci function returning errors
  • ADO and msqli connections very slow
  • Marklogic : Query response time is very high
  • Ionic 2 storage is not cleaning up on uninstall - Only for signed APK
  • How to use remove-erase idiom for removing empty vectors in a vector?