Formula for dynamic column

[Mufflon]

Hi! I have a problem creating a formula for a dynamic column in my database.

A condensed version of the table below. What I want is a formula that checks if there are any shared ranks (same ranks) withing a segment, and if there is, put a star (*) infront of that rank. In my original table there are more critera than just "Segment", but hopefully any formula that could solve the below table can scale up.

Rank	Segment	Rank Adjusted (DYNAMIC COLUMN)
1	ALL	*1
1	ALL	*1
2	ALL	*2
3	ALL	3
2	ALL	*2
1	RE	1
2	RE	2
3	RE	*3
3	RE	*3

<tbody>
</tbody>

All the best!

 

[VBA Geek]

Hello. Does this do what you want?

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=LOOKUP(<font color="Blue">COUNTIFS(<font color="Red">$A$2:$A$10,A2,$B$2:$B$10,B2</font>),{1,2},{"","*"}</font>)&A2</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C
1	Rank	Segment	Rank Adjusted (DYNAMIC COLUMN)
2	1	ALL	*1
3	1	ALL	*1
4	2	ALL	*2
5	3	ALL	3
6	2	ALL	*2
7	1	RE	1
8	2	RE	2
9	3	RE	*3
10	3	RE	*3
Sheet1

 

[Rickmaurinus]

Hi Mufflon!

How about the following formula in cell C2 (and pull it down):

=IF(   COUNTIFS($A$2:$A$10;  A2; $B$2:$B$10; B2) > 1;
       "*" & A2;
       A2)

You can easily adjust for more criteria, or for a bigger range.

Cheers,
Rick

 

[Mufflon]

Thank so much! It worked like a charm!

Regards,
Kim