Forum Discussion
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...
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
I'd modify as
=IFERROR( UNIQUE( TRANSPOSE( FILTER( 'Codes Used'!C2:INDEX( 'Codes Used'!C:C, COUNTA('Codes Used'!C:C) ), ('Codes Used'!A2:INDEX( 'Codes Used'!A:A, COUNTA('Codes Used'!A:A) )= A2 ) * ('Codes Used'!B2:INDEX( 'Codes Used'!B:B, COUNTA('Codes Used'!B:B) )=B2) ) ), 0), "no such")that significantly improves the performance.
- Hmm, I think the first way worked a little better. It looks like this formula is returning each instance of the account code. I only need to see which code is used on any given coding, not how many times it is used. See line 34 as an example.
Oh, yes, took not updated file. It shall be 1 as parameter for UNIQUE
=IFERROR( UNIQUE( TRANSPOSE( FILTER( 'Codes Used'!C2:INDEX( 'Codes Used'!C:C, COUNTA('Codes Used'!C:C) ), ('Codes Used'!A2:INDEX( 'Codes Used'!A:A, COUNTA('Codes Used'!A:A) )= A2 ) * ('Codes Used'!B2:INDEX( 'Codes Used'!B:B, COUNTA('Codes Used'!B:B) )=B2) ) ), 1), "no such")
- Interesting, is the transpose function how you get it into the other fields without having a formula? I have never seen something like this before.
- in response to how both Sergei's and my formula works is basically filtering the list for only rows that have both the INT and MIN values that are the same as the INT and MIN values on the corresponding line. The UNIQUE then filters out duplicates and the TRANSPOSE changes it from rows to columns.
