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

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