Forum Discussion

kerry590's avatar
kerry590
Copper Contributor
Jan 19, 2022

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...
Excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    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

kerry590's avatar
kerry590
Copper Contributor
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.
SergeiBaklan's avatar
SergeiBaklan
MVP
Jan 19, 2022

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")
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 19, 2022

    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's avatar
      SergeiBaklan
      MVP
      Jan 19, 2022

      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.

      • mtarler's avatar
        mtarler
        Silver Contributor
        Jan 19, 2022

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

         

Resources