ronie85

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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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
47
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top