Forum Discussion
Solved
Filtering out account codes based on other data
In my spreadsheet tab 1 "Codes" shows a list of "INT" & "MIN" codes with their corresponding name for each item. Beside them I have ten columns for account codes 1-10. On tab 2 "Codes Used" is a list...
SergeiBaklan Ok here is another question maybe you can answer. Is there a performance difference between your dynamic range:
A2:Index(....last cell...)
and
OFFSET(A2,0,0,...number of rows...)
In many cases yes. OFFSET() is so called volatile function Excel Volatile function | Exceljet which is recalculating on any change.
That's VBA discussion, but applied to the formulas as well
Excel performance - Tips for optimizing performance obstructions | Microsoft Docs
- you all are fine, I have no idea what you two are talking about but glad the question has sparked a convo between people who do. As it stands I am going to have to leave to formula as is I think. Trying to change it with so many lines is lagging the program. Plus it makes it easier to read with just the number i need to see and not the "no such" on every line.
sry about the multiple post/edit/post
I believe you need to correct the above formula to use a FIXED row 2 in each case as shown here:
=IFERROR( UNIQUE( TRANSPOSE( FILTER( 'Codes Used'!$C$2:INDEX( 'Codes Used'!C:C, COUNTA('Codes Used'!C:C) ), ('Codes Used'!$A$2:INDEX( 'Codes Used'!A:A, COUNTA('Codes Used'!A:A) )= A2 ) * ('Codes Used'!$B$2:INDEX( 'Codes Used'!B:B, COUNTA('Codes Used'!B:B) )=B2) ) ), 1), "no such")
