Remove #n/a when Index Match

[Promptbeef]

Hi this is my formula {=INDEX('All Players'!B2:B500,MATCH("PG"&'All Players'!B2,'All Players'!A2:A500&'All Players'!B2:B500,0))}

It returns the information but in between each correct cell I get #n/a

I'm moving all "PG" from one sheet name all players to a second sheet named PG. My formula brings back all players but only displays the ones that with PG. How can I change the formula so it only brings back all PG players and no #N/A?

 

[SQUIDD]

no

my bad, it leaves blanks.

dave

 

[Promptbeef]

Hey guys i tried the new formula and dragged it down but now it is blank?

@Squidd did it work for you?

 

[SQUIDD]

no

see my post above, i got the data but it left blanks between.

out of intrest, did you try the code, it works for me on your workbook

dave

 

[Promptbeef]

yes i tried the code but i need to filter pg, sg, pf, sf, and c. With a formula i can plug in the different values i don't know how to do that with the code.

 

[FDibbins]

Based on your sample (I left your "wanted" and added my calc next to it)...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

1​

[/td][td]Postition[/td][td]Name[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]PG[/td][td]Russell Westbrook[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]SG[/td][td]James Harden[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]PG[/td][td]Kyrie Irving[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]SF[/td][td]Kevin Durant[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]C[/td][td]Demarcus Cousins[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]PG[/td][td]Damian Lillard[/td][td][/td][td][/td][/tr]

[tr][td]

8​

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

[tr][td]

9​

[/td][td]Sheet 2: PG (What i want)[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

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

[tr][td]

11​

[/td][td]Position[/td][td]Name[/td][td]Mine[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]PG[/td][td]Russell Westbrook[/td][td]PG[/td][td]Russell Westbrook[/td][/tr]

[tr][td]

13​

[/td][td]PG[/td][td]Kyrie Irving[/td][td]PG[/td][td]Kyrie Irving[/td][/tr]

[tr][td]

14​

[/td][td]PG[/td][td]Damian Lillard[/td][td]PG[/td][td]Damian Lillard[/td][/tr]
[/table]
C12=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$7="PG",ROW($A$2:$A$7)),ROWS($A$1:A1))),"")
ARRAY entered using CSE
copy across, then down as needed

 

[SQUIDD]

Hi

Code if you want it, it can probably be put into a loop if needed.
As for formual route, im not sure im afraid, im terrible with array formula.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    
        Application.ScreenUpdating = False
        
            
            With Sheets("PG")
                .Range("A:B").ClearContents
            End With
            With Sheets("all players")
                .Columns("A:B").AutoFilter
                .Range("A:B").AutoFilter Field:=1, Criteria1:="PG"
                .AutoFilter.Range.Copy
            End With
            With Sheets("PG")
                .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            End With
            With Sheets("all players")
                .Columns("A:B").AutoFilter
            End With
            
                With Sheets("SG")
                    .Range("A:B").ClearContents
                End With
                With Sheets("all players")
                    .Columns("A:B").AutoFilter
                    .Range("A:B").AutoFilter Field:=1, Criteria1:="SG"
                    .AutoFilter.Range.Copy
                End With
                With Sheets("SG")
                    .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                End With
                With Sheets("all players")
                    .Columns("A:B").AutoFilter
                End With
        
                    With Sheets("PF")
                        .Range("A:B").ClearContents
                    End With
                    With Sheets("all players")
                        .Columns("A:B").AutoFilter
                        .Range("A:B").AutoFilter Field:=1, Criteria1:="PF"
                        .AutoFilter.Range.Copy
                    End With
                    With Sheets("PF")
                        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                    End With
                    With Sheets("all players")
                        .Columns("A:B").AutoFilter
                    End With
        
                With Sheets("SF")
                        .Range("A:B").ClearContents
                    End With
                    With Sheets("all players")
                        .Columns("A:B").AutoFilter
                        .Range("A:B").AutoFilter Field:=1, Criteria1:="SF"
                        .AutoFilter.Range.Copy
                    End With
                    With Sheets("SF")
                        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                    End With
                    With Sheets("all players")
                        .Columns("A:B").AutoFilter
                    End With
        
            With Sheets("C")
                    .Range("A:B").ClearContents
                End With
                With Sheets("all players")
                    .Columns("A:B").AutoFilter
                    .Range("A:B").AutoFilter Field:=1, Criteria1:="C"
                    .AutoFilter.Range.Copy
                End With
                With Sheets("C")
                    .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                End With
                With Sheets("all players")
                    .Columns("A:B").AutoFilter
                End With
        
        Application.ScreenUpdating = True
    
    End If
End Sub

 

[FDibbins]

As for formual route, im not sure im afraid, im terrible with array formula.

and Im no good with VBA lol

 

[SQUIDD]

and Im no good with VBA lol

lol

 

[Promptbeef]

Hey guys just getting home from work ill try these suggestions and let you know the outcome in a few.

 

[SQUIDD]

Updated code in a loop(shorter)

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:A")) Is Nothing Then ' change range to suit your needs
    Application.ScreenUpdating = False
    
        Dim SHT(5) As Worksheet
        Dim C(5) As String
        Set SHT(1) = Sheets("PG")
        Set SHT(2) = Sheets("SG")
        Set SHT(3) = Sheets("SF")
        Set SHT(4) = Sheets("PF")
        Set SHT(5) = Sheets("C")
        C(1) = "PG"
        C(2) = "SG"
        C(3) = "SF"
        C(4) = "PF"
        C(5) = "C"

            For I = 1 To 5
    
                With SHT(I)
                    .Range("A:B").ClearContents
                End With
                    With Sheets("all players")
                        .Columns("A:B").AutoFilter
                        .Range("A:B").AutoFilter Field:=1, Criteria1:=C(I)
                        .AutoFilter.Range.Copy
                    End With
                        With SHT(I)
                            .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                :=False, Transpose:=False
                        End With
                With Sheets("all players")
                    .Columns("A:B").AutoFilter
                End With
            Next
    Application.ScreenUpdating = True
End If
End Sub

good luck

dave