Search code examples
excelexcel-formulaconcatenationpivot-tableexcel-2013

Concatenate based on cell value

I have a table in Excel composed of three columns:

Status  Emp    Achievement
1       Emp1      98.44%
2       Emp2      91.44%
3       Emp3      82.54
2       Emp4      89.47
2       Emp5      89.02

I need to out each status with the corresponding emp and achievement as shown:

Emp1 = 98.44%
Emp2 = 91.44% , Emp4 = 89.47% , Emp5 89.02%
Emp3 = 82.54%

I did a For to Next loop but the result shows only the first one. Need help in the correct code.

Solution

  • However the result may be achieved with formulae.

    With a PivotTable (in case the raw data may not be sorted) with Status and Emp for ROWS and Achievement in VALUES and (for layout as in image) these formulae copied down to suit:

    in I2: =F2&" = "&TEXT(G2,"0.00%")
    in J2: =IF(E2<>"",I2,J1&" , "&I2)
    in K2: =OR(AND(E2<>"",E3<>""),AND(E2="",E3<>""))

    then filter ColumnK to select TRUE.

    SO27545823 example