0

Combine multible lists into one list

Jet Reports Historic Posts

April 06, 2010 12:53

None

Follow

Hi

I have been trying to create a function like the one below:
NL("Filter";table; field; field; NP("Union";Array1; Array2;Array3),
where Array1 to 3 points to three cells, each holding a fomular like NL("Filter"; table; field; …). This doesn't work.

So how should I do that, so I as the enbd result get a list/array to use in another NL("Rows") or similar function?

best regards,
Søren

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts April 06, 2010 19:17 Official comment

  Hi Søren,
  
  You can't put an NL(Union) inside an NL(Filter). You have a couple options here. You could just put the NL(Union) in whatever formula you want to use the NL(Filter) in if you just want to filter by the results of the NL(Union). If what you want to do is filter by the results of another function which itself filters by the NP(Union) then you will need to use NL(AllUnique). The example might go something like this:
  
  B3: =NL("filter","Customer","Name","Name","A*")B4: =NL("filter","Customer","Name","Name","B*")B5: =NL("Rows","Cust. Ledger Entry",,"Customer No.",NL("AllUnique","Customer","No.","Name",NP("Union",$B$3,$B$4)))
  Note that NL(AllUnique) is like NP(Union) in that it returns an Excel array and thus must be embedded in its parent function rather than being in a separate cell. Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 06, 2010 14:26

  Hi Søren,
  
  Hmmm, I'm not sure exactly what you're doing wrong, but something like what you presented should work. I created a very simple example like the following:
  
  B3: =NL("filter","Customer","Name","Name","A*")B4: =NL("filter","Customer","Name","Name","B*")B5: =NL("Rows","Customer","Name","Name",NP("Union",$B$3,$B$4))
  This works correctly and gives me customers where the name starts with either A* or B*. Obviously this is a simple example, but using NP(Union) to combine the results of 2 arrays should work correctly as a filter. To test what you are getting, you could always do:
  
  =NL("Rows",NP("Union",[CellReference1],[CellReference2],etc.))
  This would replicate the union of your arrays, so you could see the results that were being applied as the filter, which might help you figure out the problem.
  
  Also, you probably know this, but NP(Union) MUST be embedded in its parent function. You can't put the NP(Union) in one cell and then reference it from another cell; because of the nature of Excel arrays, this will not work. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 06, 2010 18:50

  Hi Hughes
  
  Thanks for your help. What you describe works for me too :-).
  
  However if you change the formular in B5 (in your example) from NL(Rows … to NL(Filter … I cannot get it to work and I get the error message:
  "NL(AllUnique) and arrays cannot be used as filters in an NL(Filter) function. Use NL(Filter) instead." (Jet version 7.1.2)
  
  It states to use NL(filter) instaed, but I don't know how to achieve that. I want the result to be a list (filter), not rows.
  
  Søren

• 0
  
  Jet Reports Historic Posts April 06, 2010 19:57

  Hi Hughes
  
  Thanks again for the reply. I will try your suggestion and see how that goes. But as I see it from your answer there is no way to split your formula in cell C5 into two formulas (one to generate the filter and one to generate the rows), as they need to be combined into one as you describe.
  
  That was what I was looking for.
  
  Søren