smart formulas

[ronie85]

I have a list of football results [TABLE="width: 218"]
<tbody>[TR]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Aston Villa
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[/TR]
[TR]
[TD]West Brom
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Sunderland
[/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[/TR]
[TR]
[TD]West Ham
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tottenham
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[/TR]
[TR]
[TD]Arsenal
[/TD]
[TD] 2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Crystal Palace
[/TD]
[TD]1
[/TD]
[TD] 2
[/TD]
[/TR]
[TR]
[TD]Man Utd
[/TD]
[TD] 1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Swansea
[/TD]
[TD]2
[/TD]
[TD] 1
[/TD]
[/TR]
[TR]
[TD]Leicester
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]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'.
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

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.

 

[ronie85]

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.

 

[Gilliam]

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

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

 

[FDibbins]

Did you use CTRL SHT ENTER to enter?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Stoke[/td][td]0[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Aston Villa[/td][td]1[/td][td]0[/td][td][/td][td]Stoke[/td][td]

0​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]West Brom[/td][td]2[/td][td]2[/td][td][/td][td]Stoke[/td][td]

10​

[/td][td]

7​

[/td][/tr]

[tr][td]

4​

[/td][td]Sunderland[/td][td]2[/td][td]2[/td][td][/td][td]Stoke[/td][td]

2​

[/td][td]

2​

[/td][/tr]

[tr][td]

5​

[/td][td]West Ham[/td][td]0[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Tottenham[/td][td]1[/td][td]0[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Arsenal[/td][td]2[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Crystal Palace[/td][td]1[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Man Utd[/td][td]1[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Swansea[/td][td]2[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Leicester[/td][td]2[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Stoke[/td][td]10[/td][td]7[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]Aston Villa[/td][td]1[/td][td]0[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]Stoke[/td][td]2[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]Sunderland[/td][td]2[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]West Ham[/td][td]0[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]Tottenham[/td][td]1[/td][td]0[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

18​

[/td][td]Arsenal[/td][td]2[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

19​

[/td][td]Crystal Palace[/td][td]1[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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