Index, value that match a condition defined by adjacent cell, copy-paste onto another column and skip blanks

[shamjamali]

Hi everyone, new forum user here with some knowledge on vba and excel. This has been a great resource for me to use excel effectively and am hopeful if someone can help me with a long time problem of mine;

Format: Excel Online
Code: Excel Formula

I am trying to figure out a function that's simple to think but hard to implement. I have tried troubleshooting using various codes and combination from other sources to no result. The problem I am facing is a variation of this problem;
https://www.mrexcel.com/forum/excel...ment-false-check-next-row-true-condition.html

I am currently using the code provided there to use in my function but its not working.

This is my excel layout:

 B      C      D

2  Score W/L  Sort Wins
3  100   Win     50
4  200   Win     100
5  500   Win     100
6  400   Win     105
7  300   Win     130
8  40    Lose    140
9  50    Lose    200
10 200   Win     200
11 250   Win     200
12 105   Lose    250
13 550   Win     300
14 600   Win     300
15 700   Win     400
16 400   Win     400
17 300   Win     500
18 200   Win     550
19 100   Lose    600
20 140   Lose    700
21 130   Lose   
22 20    Lose

AS you can see, I want to sort out the wins from the losses in column C based on game score. So far I have found the code from the link above to work close to my desired result, but it only seemed to sort some of the 'wins' and even misread some 'losses' as 'wins', This is a tough one for me

<code>=

IFERROR(INDEX(B$3:B$22,SMALL(IF(C$3:C$22="Win",ROW(B$3:B$22)-ROW(B$3)+1),ROW(B1))),"")
</code>

Update: So I have troubleshoot this thoroughly and it seems that it would not work at cells other than A1:E12...any ranges beyond this will not work properly or not work at all, the final result will end in the 'value-if-error' of the iferror function. I will need it to work at a different range other than A1:E12.
I have tried dissecting each formula to test and the problem seemed to stem from the "small" function and its nested functions. I am able to produce correct results in small scale tests, but as soon as I add nested functions, it will not work.

Looking for some pointers on what went wrong with this code.

<tbody>
</tbody>

 

[shamjamali]

Hello,

I am sorry but there seems to not be an edit button available. I have read my own post and it seems to be hastily written. Apologies for that.

I will rephrase my query below for clarification and better explanation.

This is an experiment with a game where at the end of it, you will receive a score. Based on that score, you might win or lose the game.My objective is to find out the correlation of wins to score,so first I will need to sort the wins from the losses in Column D.

Columns B and C are manual input. Column D are automated input, thus its where the code I referenced below will be implemented. The problem lies in the formula being used here as its not resulting in my desired results.
In my excel layout below, you see that Column D is where I want the winning score results to be.

But its not sorting out the winning score from the losses accurately; the resulting scores in column D is coming from both wins and losses at random, for example, in the layout below; Cell D3 is referenced from cell B9 which is actually a Loss. Cell D18 is referenced from cell B13.


The excel layout below is what is currently the results shown in column D and is not the desired outcome.

B      C      D

2  Score W/L  Sort Wins
3  100   Win     50
4  200   Win     100
5  500   Win     100
6  400   Win     105
7  300   Win     130
8  40    Lose    140
9  50    Lose    200
10 200   Win     200
11 250   Win     200
12 105   Lose    250
13 550   Win     300
14 600   Win     300
15 700   Win     400
16 400   Win     400
17 300   Win     500
18 200   Win     550
19 100   Lose    600
20 140   Lose    700
21 130   Lose   
22 20    Lose

This is what the results in Column D should look like;

B      C      D

2  Score W/L  Sort Wins
3  100   Win     100
4  200   Win     200
5  500   Win     500
6  400   Win     400
7  300   Win     300
8  40    Lose    200
9  50    Lose    250
10 200   Win     550
11 250   Win     600
12 105   Lose    700
13 550   Win     400
14 600   Win     300
15 700   Win     200
16 400   Win     
17 300   Win     
18 200   Win     
19 100   Lose    
20 140   Lose    
21 130   Lose   
22 20    Lose

The formula I placed in Column D to generate the results currently that is not generating the correct results;

IFERROR(INDEX(B$3:B$22,SMALL(IF(C$3:C$22="Win",ROW(B$3:B$22)-ROW(B$3)+1),ROW(B1))),"")

The following explanation is what I have done to troubleshoot the formula I used above. I have tried dissecting each function within formula to test. I am able to produce the correct results I wanted in small scale tests for each of the functions, but as soon as I nest the functions in the formula, it will not work.
I am aware that it is an array formula and have used Ctrl Shift Enter.

Again. Looking for some pointers on what went wrong with this formula. Thank you for your help.

Just to note, I can't use VBA as its Excel Online based.