Extract and concatenate data based on positive/negative data

[goldfield]

Hi Team,

I am looking for a solution to extract data from different cells in a row based on whether they are positive or negative (rise or fall in categories), then concatenate this data into a commentary line.

Please see simplified sample attached.

Thanks in Advance!

[kersplash]

What should it say if all are positive or negative?

[goldfield]

Hi Kersplash,
I would be more than happy for it to leave the other portion out in those cases.
ie. Sales have fallen (or risen) in State 2 (-2%), State 4 (-15%), State 5 (-7%).

Cheers

[kersplash]

What about if a state is 0%?

[kersplash]

This might be better suited to VBA.

[goldfield]

Your most likely right about it being better suited to VBA but I was hoping to achieve it with formulas.
There is no real need for 0% to be reported.

[JeteMc]

Not exactly the expected outcome, but perhaps close enough.
E6:E11 are populated using: =AGGREGATE(15,6,E$2:J$2,ROWS(A$1:A1))
D6:D11 are populated using: =INDEX(E$1:J$1,MATCH(E6,E$2:J$2,0))
F6:F11 are populated using: ="("&TEXT(E6,"0.0%")&")"
G6:G11and H6:H11 are populated using formulas similar to: =IF(AND(E5<0,E6<0),I5&", "&D6&" "&F6,IF(E6<0,D6&" "&F6,""))
I6:I11 and J6:J11 are populated using formulas similar to: =IF(G6<>"",I$5&" "&G6,"")
C13 is populated using: =INDEX(I6:I11,AGGREGATE(14,6,(ROW(I6:I11)-ROW(I5))/(I6:I11<>""),1))&INDEX(J6:J11,AGGREGATE(14,6,(ROW(J6:J11)-ROW(J5))/(J6:J11<>""),1))
Let us know if you have any questions.