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

mtarler's avatar
mtarler
Silver Contributor

SergeiBaklan 

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

 

mtarler's avatar
mtarler
Silver Contributor
Jan 20, 2022

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")
  • kerry590's avatar
    kerry590
    Copper Contributor
    Jan 20, 2022
    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.

Resources