Array - Index - iferror formula

[ronie85]

I have a list of football results
[TABLE="width: 218"]
<tbody>[TR]
[TD]Stoke (cell K8)
[/TD]
[TD]0 (cell L8)
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Aston Villa (cell K9)
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]West Brom (cell K10)
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Sunderland (cell K11)
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Stoke (cell K12)
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Tottenham (cell K13)
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Arsenal (cell K14)
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Stoke (cell K15)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Man Utd (cell K16)
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Swansea (cell K17)
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Leicester (cell K18)
[/TD]
[TD]2 (cell L18)
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

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 with 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.
I have been told to use formula =IFERROR(INDEX(A$1:A$20,SMALL(IF($A$1:$A$20="Stoke",ROW($A$1:$A$20)),ROWS($A$1:A1))),"") but it doesn’t seem to work. Can someone help me please?

 

[FDibbins]

Ronie, if you are posting on multiple forums/boards, it is expected that you include a link to the other forums/threads. That way members dont waste their time answering a question that has already been answered

 

[Aladin Akyurek]

FDibbins is right... That said, the suggested solution is not robust, also not efficient.

[TABLE="width: 445"]
<TBODY>[TR]
[TD="class: xl63, width: 146, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Stoke
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Stoke
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Stoke
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Aston Villa
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Stoke
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]West Brom
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]Stoke
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Sunderland
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Stoke
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]0
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Tottenham
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Arsenal
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Stoke
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Man Utd
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Swansea
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 146, bgcolor: white"]Leicester
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

W6 houses a value like Stoke.

W8, just enter, copy across to Y8, then copy down:

=IF($Z8="","",INDEX(K$8:K$18,$Z8))

Z8, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($K$8:$K$18=$W$6,ROW($K$8:$K$18)-ROW($K$8)+1),
  ROWS($Z$8:Z8)),"")

 

[Rick Rothstein]

Here is a VBA macro for you to consider...

Sub GetTeam()
  Dim R As Long, C As Long, X As Long, Team As String, Data As Variant, TeamData As Variant
  Team = "Stoke"
  Data = Range("K8:M" & Cells(Rows.Count, "K").End(xlUp).Row)
  ReDim TeamData(1 To 38, 1 To 3)
  For R = 1 To UBound(Data)
    If Data(R, 1) = Team Then
      X = X + 1
      TeamData(X, 1) = Data(R, 1)
      TeamData(X, 2) = Data(R, 2)
      TeamData(X, 3) = Data(R, 3)
    End If
  Next
  Range("W8:Y45") = TeamData
End Sub

 

[ronie85]

Array Index iferror

I have tried to reply to my post from last night but it won't give me the option. The post is http://www.mrexcel.com/forum/excel-questions/805181-array-index-iferror-formula.html
The issue I'm having now after trying Aladins post is; Thanks Aladin. I've inserted the data but it has all remained blank. Here is what ive put in.
Cell W8 =IF($Z8="","",INDEX(K$8:K$767,$Z8))
I dragged this across 2 cells and then down 38.
Then in cell Z8 =IFERROR(SMALL(IF($K$8:$K$767=$W$6,ROW($K$8:$K$767)-ROW($K$8)+1),ROWS($Z$8:Z8)),"")
Note that the cells I am copying data from are the results of formulas. Basicailly K8 That shows the value "Stoke" really has a formula =INDEX(IF(MOD(ROWS(K$1:K7), 2),F:F,I:I ), INT((ROWS(K$1:K7)-1)/2) + 1) would this affect the array? Even if its possible to forward my worksheet to someone to view.

 

[Aladin Akyurek]

Re: Array Index iferror

I have tried to reply to my post from last night but it won't give me the option. The post is http://www.mrexcel.com/forum/excel-questions/805181-array-index-iferror-formula.html
The issue I'm having now after trying Aladins post is; Thanks Aladin. I've inserted the data but it has all remained blank. Here is what ive put in.
Cell W8 =IF($Z8="","",INDEX(K$8:K$767,$Z8))
I dragged this across 2 cells and then down 38.
Then in cell Z8 =IFERROR(SMALL(IF($K$8:$K$767=$W$6,ROW($K$8:$K$767)-ROW($K$8)+1),ROWS($Z$8:Z8)),"")
Note that the cells I am copying data from are the results of formulas. Basicailly K8 That shows the value "Stoke" really has a formula =INDEX(IF(MOD(ROWS(K$1:K7), 2),F:F,I:I ), INT((ROWS(K$1:K7)-1)/2) + 1) would this affect the array? Even if its possible to forward my worksheet to someone to view.

See the workbook that implements the proposed set up:
https://dl.dropboxusercontent.com/u/65698317/ronie85%20sublist.xlsx

 

[ronie85]

OK This works in a workbook where I type the team names. The problem is that in my sheet the source K8 that states 'Stoke' is actually a formula =INDEX(IF(MOD(ROWS(K$1:K7), 2),F:F,I:I ), INT((ROWS(K$1:K7)-1)/2) + 1) so the new array doesn't recognise this. is there a way I can make it recognise it? I have also tried doing =K8 and adjusting the formula but it still doesn't recognise it as the source is a formula and not 'Stoke' typed in.

 

[Aladin Akyurek]

OK This works in a workbook where I type the team names. The problem is that in my sheet the source K8 that states 'Stoke' is actually a formula =INDEX(IF(MOD(ROWS(K$1:K7), 2),F:F,I:I ), INT((ROWS(K$1:K7)-1)/2) + 1) so the new array doesn't recognise this. is there a way I can make it recognise it? I have also tried doing =K8 and adjusting the formula but it still doesn't recognise it as the source is a formula and not 'Stoke' typed in.

1) Where is the formula entered?
2) Is it copied down?
3) What do you have in F?
4) What do you have in I?
5) What is the formula supposed to do?

 

[ronie85]

1. The formula is entered in J8

1. Yes it has been copied down to J767
2. F is another formula =LEFT(B4,FIND("-",B4)-2) that shows team names (Home teams). This is also copied down.
3. I is another formula =MID(B4,FIND(" ",B4,FIND("-",B4))+1,99) that also shows team names (Away Teams). This is also copied down.

F,G,H & I look like this
[TABLE="width: 237"]
<tbody>[TR]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD]Aston Villa
[/TD]
[/TR]
[TR]
[TD]West Brom
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[TD]Sunderland
[/TD]
[/TR]
[TR]
[TD]West Ham
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD]Tottenham
[/TD]
[/TR]
[TR]
[TD]Arsenal
[/TD]
[TD] 2
[/TD]
[TD]1
[/TD]
[TD]Crystal Palace
[/TD]
[/TR]
</tbody>[/TABLE]

1. The formula =IFERROR(SMALL(IF($V$8:$V$767=$AB$6,ROW($V$8:$V$767)-ROW($V$8)+1),ROWS($AE$8:AE8)),"") in AE8 and the formula =IF($AE8="","",INDEX(V$8:V$767,$AE8)) that is dragged from AB8 to AD8 and down to AD37 are supposed to go through a list of 760 football matches. In this case I am looking at ‘Stoke’ as an example. I want the formula in AE8 to go through the list of 760 football matches and only show the 38 times that ‘Stoke’ appears. Then on the right of ‘Stoke’ will be the result (Goals for in AC8-AC37, and Goals Against in AD8-AD37). The problem that I am having is that the above formula won’t recognise the team names, and probably not the scores either, because they are formulas and not typed in as Stoke 1 0. I have also tried using =on the said cell and changing the formula to that cell but it still doesn’t work. I don’t want to use a macros or filter as I want the sheet to have live updating so that when I paste in new results from a website on a weekly basis, it automatically updates all the results, in order of team, and it will also be in order of date as I will be pasting them in over time.

I’m not asking for much J

 

[ronie85]

[TABLE="width: 651"]
<tbody>[TR]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Stoke
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Overall
[/TD]
[TD]For
[/TD]
[TD]Against
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD]Stoke
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aston Villa
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[TD]Aston Villa
[/TD]
[TD]1
[/TD]
[TD] 0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West Brom
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[TD]West Brom
[/TD]
[TD] 2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunderland
[/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[TD]Sunderland
[/TD]
[TD]2
[/TD]
[TD] 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West Ham
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD]West Ham
[/TD]
[TD] 0
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have stated the column names above. ‘Overall’ is in cell J7 for reference. If I change the 'Stoke' in AB6 to 'Aston Villa' the details don't change. Also I have other 'Stoke' further down the list and they don't show up. I have tried this on a separate sheet where there are no formulas, just typed in, and it works fine.