Filtering out account codes based on other data

[kerry590]

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.

[#2 · Accepted solution]

@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

[#3]

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.

[#4]

@mtarler 

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.

[#5]

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.

[#6]

@kerry590 

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")

[#7]

@Sergei Baklan 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. 

[#8]

@mtarler 

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.

[#9]

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.

[#10]

@Sergei Baklan 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...)

[#11]

@mtarler 

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

[#12]

@Sergei Baklan 

Oh thx. I knew INDIRECT was volatile but forgot that OFFSET is also volatile.

[#13]

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")

[#14]

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.