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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Segment[/TD]
[TD]Rank Adjusted (DYNAMIC COLUMN)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ALL[/TD]
[TD]*1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ALL[/TD]
[TD]*1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ALL[/TD]
[TD]*2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ALL[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ALL[/TD]
[TD]*2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RE[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RE[/TD]
[TD]*3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RE[/TD]
[TD]*3[/TD]
[/TR]
</tbody>[/TABLE]

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