# Concatenate results

• Guys,

I need some help on gathering information to present on a report.

This file tracks issues, they are all numbered and they are also classified (A, B, C, D, M, NA). Also they have status and here is the problem I am trying to fix:

This formula to return values of the number of issues that meets a certain status and class:

Any issue classified as "B" and with status Waiting to Be Cleared then it would return: "Issues 129 and 300 are waiting to be cleared", where 129 and 300 would be the result of the formula I cannot find.The rets is discarded.

The attached file can show you better.

## Files

DiogoCuba
"Give a man a fish, he'll eat for a day.
Teach a man to fish, he'll eat for a lifetime.
Give a man religion, he'll die praying for a fish."

• You will need some VBA here.

Then in G2 of your sheet enter this Array* Formula:

="Issue"&IF(COUNTIFS(\$B\$2:\$B\$13,F2,\$C\$2:\$C\$13,"waiting to be clear")=1," ","s ")&SUBSTITUTE(TRIM(aconcat(IF(\$B\$2:\$B\$13=F2,IF(\$C\$2:\$C\$13="waiting to be clear",\$A\$2:\$A\$13,""),"")," "))," ",",")&IF(COUNTIFS(\$B\$2:\$B\$13,F2,\$C\$2:\$C\$13,"waiting to be clear")=1," is "," are ")&"waiting to be cleared"

copied down

You will need to save the file as a .xlsm file

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Thanks a million! It worked like a charm!

DiogoCuba
"Give a man a fish, he'll eat for a day.
Teach a man to fish, he'll eat for a lifetime.
Give a man religion, he'll die praying for a fish."

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!