Forum Discussion

kerry590's avatar
kerry590
Copper Contributor
Jan 19, 2022
Solved

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 19, 2022

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.

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

Resources