Return an alternate value when several criteria are met within a cell range with common IDs

Question

I have a table with several rows featuring an ID column, a Value column and a RealValue column.

The challenge is, within the "Values" with the same ID, if they are consistent, the RealValue remains the same as Value; if they are not consistent within the same Value, a character is added and displayed in the RealValue column.

I hope the file in the link below explains what I mean: https://dl.dropboxusercontent.com/u/71954/CHALLENGE.xlsx:

    ColumnA     ColumnB ColumnC
Row1    ID      Value   RealValue (Target)
Row2    1       A       Ax
Row3    1       B       Bx
Row4    1       A       Ax
Row5    1       B       Bx
Row6    1       A       Ax
Row7    1       B       Bx
Row8    1       A       Ax
Row9    1       B       Bx
Row10   2       A       A
Row11   2       A       A
Row12   2       A       A
Row13   2       A       A
Row14   2       A       A
Row15   3       B       B
Row16   3       B       B
Row17   3       B       B
Row18   3       B       B
Row19   7       A       Ax
Row20   7       C       Cx
Row21   7       A       Ax
Row22   7       C       Cx
Row23   7       A       Ax
Row24   7       C       Cx
Row25   7       A       Ax
Row26   7       C       Cx
Row27   5       C       C
Row28   5       C       C
Row29   5       C       C
Row30   5       C       C
Row31   6       B       B
Row32   6       B       B
Row33   6       B       B
Row34   6       B       B
Row35   6       B       B
Row36   6       B       B
Row37   6       B       B
Row38   6       B       B
Row39   6       B       B
Row40   6       B       B

pnuts pnuts · Accepted Answer · 2013-09-19T19:53:31

I am not quite sure what your question is, but if it is to create ColumnC as in your example from ColumnsA and B, I suggest:

=IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2),B2,B2&"x")

in Row2 and copied down to suit.

Return an alternate value when several criteria are met within a cell range with common IDs

1 Answers