0

Summary Replicator on Custom Table

Chris Berry

August 16, 2018 22:04

None

Follow

Hi Jet,

I know that specifying a field name on a rows replicator will "unique-ify" the list of results brought back. 

Is there any way to get a rows replicator to do this when passing it an array for a table name instead of a database table?

I.e. - B1 = 1, B2 = 2, B3 = 2

Currently =NL("Rows", B1:B3) returns 1,2,2 and NL("Rows", B1:B3, B1:B3) returns the same. Is there any syntax I can use to only return two rows of 1,2?

Again, this would be for custom table ranges. --or is my problem that I need to get the rows function to recognize it as a table and not an array?

Thanks!

• Facebook
• Twitter
• LinkedIn

4 comments

• 1
  
  Harry Lewis August 16, 2018 22:23

  Hi Chris -

  Since the Jet Excel add-in is designed to show everything in the specified range of cells, the only workaround that comes to mind (and it only works if your range doesn't have value gaps) would be something like this:

  

• 0
  
  Chris Berry August 17, 2018 13:37

  Hi Harry,

  Thanks for taking the time to try to figure this out. I don't this solution will work for me but it is interesting to see some of the solutions people come up with. I think I should be able to accomplish what I need to do in this particular instance using a pivot table, but I know I've had this issue come up a couple times in the past and was really hoping there was a way to input a custom table (from a sheet in excel) in to the rows function. I think it would allow me to customize the data that much more. 

  Appreciate you ruling it out though and preventing me from going too crazy trying to figure out if it was possible on my own.

  Regards,

  -Chris

• 2
  
  Jamie L. Agee August 24, 2018 16:00

  Hi Chris. 

  If I'm understanding the scenario correctly, the following works: 

   

  =NL("Rows",NP("Union",$B$3:$B$5))

  

  ~Jamie

• 0
  
  Chris Berry August 27, 2018 15:11

  Thanks Jamie. I think that'll definitely help. 

  If possible, I think what I'm really looking for is a way to pass a Rows function a "field name" from a custom table (whether that's created using the table builder or an array or something else). 

  Apologies, I know I'm probably not being specific enough, but I believe I've come across issues in the past where this would have been helpful - not sure how I handled it then. Just curious if it was possible to assign field names to custom tables (or use column references) to be used in Rows replicators. 

  Either way, I'm certain I'll end up using your solution for the problem above in a future report. Thanks!