0

Help with Array formula or Filter [solved]

Jet Reports Historic Posts

November 09, 2011 21:32

None

Follow

I need help figuring out the correct combination of formulas. Here is a simplified version of what I'm trying to do:

Join:
Employees with a certain class code
with
Employees with a certain payroll history
exclude
Employees that meet a certain criteria using a linked table

I'm having a hard time excluding specific employees. There are a handful that need excluded and we store those exceptions in another table. I've tried linking tables but not every employee has a record in that sub-table and it's returning only employees that do have an record in the sub-table. I need it to return all employees that either don't have a record or meet the given criteria. How do I accomplish this?

Here is one of my formulas (I'm using NP(union) later on):
=NL("filter","employee","no.","status","active","class code",$F$8,"no.",NL("filter","misc. article information","employee no.","serial no.","<>dnd&<>DND","Misc. Article Code","DRV*"))

I want to be able to get this in a formula and not use the hide+? function to hide the employees. Thanks!

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts November 09, 2011 22:24

  You could use NP(Difference). NP(Difference) is a little like NP(Union) except that it returns all the values in the first list which do NOT exist in the 2nd list. Would this work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts November 10, 2011 07:51

  NL("Rows", NP("Difference", NP("Union", NL("AllUnique","Employees with a certain class code"), NL("AllUnique","Employees with a certain payroll history") ), NL("AllUnique","Employees that meet a certain criteria using a linked table") ) )
• 0
  
  Jet Reports Historic Posts November 10, 2011 15:59

  I hadn't realized I could combine NP(difference) and NP(union) like that. Thanks!