ronie85

New Member
Joined
Jan 25, 2014
Messages
44
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>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Jan 25, 2014
Messages
44
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

Active Member
Joined
Jul 10, 2014
Messages
286
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
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Did you use CTRL SHT ENTER to enter?

A​
B​
C​
D​
E​
F​
G​
1​
Stoke01
2​
Aston Villa10Stoke
0​
1​
3​
West Brom22Stoke
10​
7​
4​
Sunderland22Stoke
2​
2​
5​
West Ham01
6​
Tottenham10
7​
Arsenal21
8​
Crystal Palace12
9​
Man Utd12
10​
Swansea21
11​
Leicester22
12​
Stoke107
13​
Aston Villa10
14​
Stoke22
15​
Sunderland22
16​
West Ham01
17​
Tottenham10
18​
Arsenal21
19​
Crystal Palace12

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,488
Messages
5,529,164
Members
409,852
Latest member
Perry123
Top