smart formulas

[ronie85]

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>

 

[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?

	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