Forum Discussion
kerry590
Jan 19, 2022Copper Contributor
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...
- Jan 19, 2022
kerry590 here is the formula and fill down:
=TRANSPOSE(UNIQUE(FILTER('Codes Used'!C:C,(A2='Codes Used'!A:A)*(B2='Codes Used'!B:B),"")))
see attached
mtarler
Jan 19, 2022Silver Contributor
SergeiBaklan that is a good point but I would recommend going for formatting as a table:
=TRANSPOSE(UNIQUE(FILTER(Codes_Used[ACCOUNT],(A2=Codes_Used[INT])*(B2=Codes_Used[MIN]),"")))
That said I also notice you reversed the UNIQUE and TRANSPOSE functions in your version. I'm not seeing why and wondering if there was a specific reasoning for that.
SergeiBaklan
Jan 19, 2022MVP
1) Yes, table is much better. However, Code Used are pulled from the database, I'm not sure which technique is used for that and is it worth compare to dynamic range. Plus, form performance point of view dynamic range are more preferable. At least that was before, perhaps it is improved in modern Excel.
2) Order of functions - nothing is behind. Just started with this variant, when had phone call and after return your post is appeared. Decided to post my one accenting on performance.
- mtarlerJan 19, 2022Silver Contributor
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...)
- SergeiBaklanJan 19, 2022MVP
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
- mtarlerJan 20, 2022Silver Contributor