Creating a unique list using multiple variables

[GuyGadois]

I have the following on my worksheet (I excluded columns that don't matter for this problem). I want to make to unique hardcoded lists to the right of the main list (bolded).


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

C​

[/TD]
[TD]

T​

[/TD]
[TD]

U​

[/TD]
[TD]

​

[/TD]
[TD]

W​

[/TD]
[TD="align: center"]X[/TD]
[TD]

Y​

[/TD]
[TD] [/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name[/TD]
[TD]Action[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Action[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Action[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]Buy[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Buy[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Sell All[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]Sell All[/TD]
[TD]66[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]Buy[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]Sell[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]Sell[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]D[/TD]
[TD]Buy[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first list (W,X,Y) creates a list of all Name=Buy and the corresponding Action and Value. I got that to work thanks to the help of someone on MrExcel! Here is the successful W column formula it looks like:

=IFERROR(INDEX(C:C,SMALL(IF($T$4:$T$200="Buy",ROW($C$4:$C$200)),ROWS($C$1:C1))),"")

For the next list I want to display all "Sell" and "Sell All". I can't seem to get it to work using the "Or" function within the IF statement. This is what I have that isn't working. Can someone help me solve this one. I've been working it on and off for 2 days and I can't seem to figure it out...

Here is the unsuccessful code for column AA looks like:

=IFERROR(INDEX(C:C,SMALL(IF(OR($T$4:$T$200="Sell",$T$4:$T$200="Sell All"),ROW($C$4:$C$200)),ROWS($C$1:C2))),"")

Cheers,

GG

 

[V_Malkoti]

OR is going to return a single value, not an array, so all cells will probably end up showing same value.

Try using LEFT or + operator to create an array for condition. Modifying "Buy" value formula for examples:

=IFERROR(INDEX(C:C,SMALL(IF([COLOR=#0000ff]LEFT($T$4:$T$200, 4)="Sell")[/COLOR],ROW($C$4:$C$200)),ROWS($C$1:C1))),"")

=IFERROR(INDEX(C:C,SMALL(IF([COLOR=#0000ff]($T$4:$T$200="Sell")*1+($T$4:$T$200="Sell All")*1[/COLOR],ROW($C$4:$C$200)),ROWS($C$1:C1))),"")

 

[Aladin Akyurek]

Can the record say A and Buy occur more than once in columns C and T?