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

mtarler's avatar
mtarler
Silver Contributor
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
MVP
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
      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. 

Resources