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.

  • You will need some VBA here.

    add this module to your VB editor:

    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


    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!

    "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!