# smart formulas

#### ronie85

##### Board Regular
I have a list of football results
 Stoke 0 1 Aston Villa 1 0 West Brom 2 2 Sunderland 2 2 West Ham 0 1 Tottenham 1 0 Arsenal 2 1 Crystal Palace 1 2 Man Utd 1 2 Swansea 2 1 Leicester 2 2 These results continue and the team names appear over and over again. what I need is a formula to select for talk sake say 'Stoke'. and list everytime a result of theirs is listed, in order of the first that is listed to the last that is listed. There will be 38 in total. Note that I don't want to use a filter. The results are being pasted from a website so I want to formula to select each result for 'stoke'.

<tbody>
</tbody>

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Not sure where you want to put this, but this ARRAY formula will give you what you want. I have hard-coded "Stoke", but it may be better to put the name in its own cell and reference it.

=IFERROR(INDEX(A\$1:A\$20,SMALL(IF(\$A\$1:\$A\$20="Stoke",ROW(\$A\$1:\$A\$20)),ROWS(\$A\$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Looks good but I can't get it to work. I should have explained more. The first entrance of 'Stoke' is in cell K8 and the it will reappear 37 more times between cells K8:K767. So I need these 38 "Stoke" to appear in the order they are in Column K, Starting in cell W8 and running to cell W45. I also need the next 2 cells to the right to contain the data too. So W8=Stoke X8=0 Y8=1. Then the next row starting at W9 will say Stoke, but X9 & Y9 will have two different numbers depending on the next time stoke appears between K8:K767. Note that the data that will be in X8 comes from L8, and the data from Y8 comes from M8. I hope this makes sense as its a lot to take in.

Looks good but I can't get it to work. I should have explained more. The first entrance of 'Stoke' is in cell K8 and the it will reappear 37 more times between cells K8:K767. So I need these 38 "Stoke" to appear in the order they are in Column K, Starting in cell W8 and running to cell W45. I also need the next 2 cells to the right to contain the data too. So W8=Stoke X8=0 Y8=1. Then the next row starting at W9 will say Stoke, but X9 & Y9 will have two different numbers depending on the next time stoke appears between K8:K767. Note that the data that will be in X8 comes from L8, and the data from Y8 comes from M8. I hope this makes sense as its a lot to take in.

If you'd like a Macro to do this for you, here's one i drew up quickly
Code:
``````Sub teamfilter()Dim x, xRow As Long
Dim myTeam As String

Range("W8:Y45").ClearContents
myTeam = InputBox("Enter team name")
For x = 8 To ThisWorkbook.Sheets(1).Cells(Rows.Count, 11).End(xlUp).Row
If Range("K" & x).Value = myTeam Then
xRow = Sheets(1).Cells(Rows.Count, "W").End(xlUp).Row + 1
Select Case xRow
Case 2
Range("W8") = Range("K" & x).Value
Range("X8") = Range("L" & x).Value
Range("Y8") = Range("M" & x).Value
Case Else
Range("W" & xRow) = Range("K" & x).Value
Range("X" & xRow) = Range("L" & x).Value
Range("Y" & xRow) = Range("M" & x).Value
End Select
End If
Next
End Sub``````

cheers,
Matt

Did you use CTRL SHT ENTER to enter?

 A​ B​ C​ D​ E​ F​ G​ 1​ Stoke 0 1 2​ Aston Villa 1 0 Stoke 0​ 1​ 3​ West Brom 2 2 Stoke 10​ 7​ 4​ Sunderland 2 2 Stoke 2​ 2​ 5​ West Ham 0 1 6​ Tottenham 1 0 7​ Arsenal 2 1 8​ Crystal Palace 1 2 9​ Man Utd 1 2 10​ Swansea 2 1 11​ Leicester 2 2 12​ Stoke 10 7 13​ Aston Villa 1 0 14​ Stoke 2 2 15​ Sunderland 2 2 16​ West Ham 0 1 17​ Tottenham 1 0 18​ Arsenal 2 1 19​ Crystal Palace 1 2

E2=IFERROR(INDEX(A\$1:A\$20,SMALL(IF(\$A\$1:\$A\$20="Stoke",ROW(\$A\$1:\$A\$20)),ROWS(\$A\$1:A1))),"")
copied down and across

Replies
19
Views
1K
Replies
1
Views
272
Replies
3
Views
489
Replies
1
Views
606
Replies
3
Views
270

1,217,677
Messages
6,137,936
Members
450,099
Latest member
Pushbutton

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back