0

NL Rows and NP Union with an Account Category Filter

Cliff Earnshaw

March 18, 2019 15:45

None

Follow

I have 8 NL("Filter") functions and I want to combine them using a nested NP("Union") function and add one filter. Here's an example of one of the 8 NL functions:

=NL("Filter","Accounts","Segment1","Company=","Crimson Gulf, LLC","Segment1",$D$3,"Segment2",$D$4)

I have 8 of these, one for each Company. Then I union all 8 and return a list of rows for Segment1 with the following:

=NL("Rows",NP("Union",$C$8,$C$9,$C$10,$C$11,$C$12,$C$13,$C$14,$C$15))

Everything works fine up until this point. I want to add in a filter for Account Category to the NL("Rows",NP("Union") function of Segment 1 rows.

How do I include a filter for the Account Category within the NL("Rows",NP("Union")) function?

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Bryan Robinson March 18, 2019 15:47

  Hi Cliff,  it is not possible to add a filter to the NL(Rows) function in your example.  You will need to add the filter to each of your NL(Filter) functions so that you are filtering at the source of the data gathering.

• 0
  
  Cliff Earnshaw March 18, 2019 15:51

  Hi Brian, is there anywhere else to place the Account Category filter? I would have to create up to 200+ NL Filter functions (8 companies times 25+ Account Categories) to accommodate all of the different Account Categories and Company combinations.

• 0
  
  Cliff Earnshaw March 18, 2019 15:51

  sorry for the misspelling, Bryan*

• 0
  
  Bryan Robinson March 18, 2019 15:58

  Not sure I understand the issue.  Couldn't you change your NL(Filter) to something like =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$5,"Company=",$D$6) 

  Add that to each of your NL(Filter) functions and it will be a filter for the Category on each function.  Update all the cells to run the report for a new combination

• 0
  
  Cliff Earnshaw March 18, 2019 16:43

  If I changed the filter as you mentioned, each company would have to have multiple filters such as the following:

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$5,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$6,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$7,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$8,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$9,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$10,"Company=",$D$6)

  =NL("Filter","Accounts","Segment1","Segment1",$D$3,"Segment2",$D$4,"Account Category Number",$D$11,"Company=",$D$6)

  Then it would go to the next of 8 companies and repeat all of the different Account Categories. There would be hundreds of filters.

  I am trying to create dynamic grouped rows of consolidated Segment1 information by each Account Category and then pull in consolidated balances by each Segment1.

   

• 0
  
  Cliff Earnshaw March 18, 2019 17:00

  Maybe this will help. I have all my filters in cells C8 through C15. I have used a Union to consolidate all companies for Segment1 in C28 which shows in the formula bar.

  For Labor and Benefits, I want all Segment1's to repeat in rows for Account Category=63 and then be subtotaled. Then for Office Expenses, I want the same thing, but only Segment1's for Account Category=64. This will continue for Account Categories = 63 through 95. See below for a rough screen shot:

  

  Is this possible?

• 0
  
  Bryan Robinson March 18, 2019 17:07

  Make sense, however, the only other solution will require all companies to have the exact same set of accounts or at least one company that has all Segment 1 that are present in every other company.

  =NL("Rows","Accounts","Segment1","Segment1",NP("Union",$C$8,$C$9,$C$10,$C$11,$C$12,$C$13,$C$14,$C$15),"Account Category Number", <<Cell reference>>,"Company=",<<Company where all seg1 exist>>)