Help

Concatenate and summarize each rollup value

Topic Labels: Formulas
Solved
Jump to Solution
699 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jill_Pierce
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all, 

I have a two-table base. The first table/screenshot below is for survey responses; each record is an individual survey response. The second table/screenshot is where the responses roll up for each survey. 

Is there a way within the rollup filed formula to achieve my highlighted desired result? I'm trying to avoid adding multiple rollup fields for each response type and then concatenating all of the information in a new column. 

Jill_Pierce_0-1701297979227.png

Jill_Pierce_2-1701298061656.png

 

 

 

2 Solutions

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hi @Jill_Pierce,

This is what it looks like when realized in a single Rollup field.

"Every day : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|","")) & " responses)\n" &
"Once a week : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|","")) & " responses)\n" &
"Never : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|","")) & " responses)\n"

 

 

 

See Solution in Thread

Jill_Pierce
5 - Automation Enthusiast
5 - Automation Enthusiast

@Sho Thank you for taking the time to write that formula! Do you have an opinion on whether the single formula within the rollup field would work best or, use several rollup fields on the summary table? 

See Solution in Thread

3 Replies 3
Sho
11 - Venus
11 - Venus

Hi @Jill_Pierce,

This is what it looks like when realized in a single Rollup field.

"Every day : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week",""),"Never",""),"|","")) & " responses)\n" &
"Once a week : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week","@"),"Never",""),"|","")) & " responses)\n" &
"Never : "&
ROUND(
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|",""))/
  LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day","@"),"Once a week","@"),"Never","@"),"|",""))*100,0
)&"% ("& LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ARRAYJOIN(values,"|"),"Every day",""),"Once a week",""),"Never","@"),"|","")) & " responses)\n"

 

 

 

Jill_Pierce
5 - Automation Enthusiast
5 - Automation Enthusiast

@Sho Thank you for taking the time to write that formula! Do you have an opinion on whether the single formula within the rollup field would work best or, use several rollup fields on the summary table? 

If you don't plan to use the several rollup field values elsewhere, I think a single rollup field is fine.