Excel FILTER with sorting, discontiguous columns and n-top values in single step-CodePudding

I have attached a screenshot of the original table and the desired result.

What I want is basically to return records from an excel table based on a single criterion (CustomerID), sort the results on another criterion (Date), attach discontiguous columns from an original table, and return only n-rows from that filtered selection (in this case last 3)?

Is there a way to do it in a single step without the use of helper formulas or helper tables including RANK, LARGE, and so on?

Thanks

CodePudding user response：

Posted, the solution as an answer as it worked for OP

You could try something like this as shown in screenshot below,

• Formula used in cell J3

=LET(_table,Table1[#All],
_headers,TAKE(_table,1),
_customerwise,FILTER(DROP(_table,1),DROP(INDEX(_table,,1),1)=K2,""),
_lastthree,TAKE(_customerwise,-3),
_requiredcols,CHOOSECOLS(_lastthree,1,4,6,7),
VSTACK(CHOOSECOLS(_headers,1,4,6,7),SORT(_requiredcols,2,-1)))

Edit, the one which was posted in comments.

• Formula in cell J9

=CHOOSECOLS(SORT(TAKE(FILTER(Table1,K2=Table1[Customer ID]),-3),4,-1),1,4,6,7)

Literally, the formulas given can be converted to a LAMBDA() to create a custom, reusable formula with a friendly name.

• Formula used in cell J3

=LISTDATA(Table1[#All],K2)

Where LISTDATA() is a custom and reusable formula with a friendly name, defined in name manager, using LAMBDA()

=LAMBDA(array,criteria,
LET(_table,array,
_headers,TAKE(_table,1),
_customerwise,FILTER(DROP(_table,1),DROP(INDEX(_table,,1),1)=criteria,""),
_lastthree,TAKE(_customerwise,-3),
_requiredcols,CHOOSECOLS(_lastthree,1,4,6,7),
VSTACK(CHOOSECOLS(_headers,1,4,6,7),SORT(_requiredcols,2,-1))))(B3:H12,K2)

Takes only array and criteria to give you required output.

=LISTDATA(array,crietria)

CodePudding user response：

Following your conditions, you can use the following under O365 in J2 the following formula:

=LET(input, TB_Inv, customerId, I2, customers, TB_Inv[Customer],
  TAKE(CHOOSECOLS(SORT(FILTER(TB_Inv, customers=customerId),4,-1),1,4,6,7),3)
)

Here is the output:

It sorts by date in descending order, then it takes the first three rows from the final output.

If you want to include the header, then:

=LET(input, TB_Inv, customerId, I2, customers, TB_Inv[Customer],
  result, TAKE(CHOOSECOLS(SORT(FILTER(TB_Inv, 
    customers=customerId),4,-1),1,4,6,7),3),
  VSTACK(CHOOSECOLS(TB_Inv[#Headers],1,4,6,7), result)
)