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 I pulled from our database that shows each INT & MIN with a corresponding account code.
What I am hoping to do is run a formula that will pull out the various account codes from tab 2 and place them in the Account 1- 10 fields on tab 1. I am thinking some type of SUMIF formula may be needed but because there are so many variables on tab 2 I am not sure how to proceed.
Now the caveat with all this is each INT & MIN code on tab 1 should only have 4 different possible account codes but because of how things were done in the past or errors made at certain times of the year it may have more or less. This is why I listed the account codes out to 10 but more could be added if necessary. My goal with this will be to eventually incorporate this data into another spreadsheet we use to error check ourselves.
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.
- 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.
