Pivot Table reading blank cells as populated...

[CerberaJQ]

Good afternoon,

I am struggling with a pivot table that I am running - the source data in it's original format is a set of index/match arrays, with an IFNA to tidy things up a bit (eg {=IFNA(INDEX('Learning Data'!$L:$L,MATCH($B2&F$1,'Learning Data'!$G:$G&'Learning Data'!$K:$K,0)),"")}

I have copy/pasted 'as values', however when I run a pivot off the set, it is still counting the blank cells... how do I avoid this without physically pressing delete on all of the blank cells?!

Any advice appreciated as always!

Cerbera

 

[CerberaJQ]

Hi all,

I've been playing with this all weekend, and still cannot figure it out... I have saved some sample data to better explain what I mean... click here!

The Centre tab is the data that I need to report on (with the index/match etc), Paste Values tab is where I have copied and pasted as values (showing the empty cells as empty), and the pivot tab has the pivot table on it - but it is counting the empty cells!

Help me Mr Excel, you're my only hope!

Cerbera

 

[RoryA]

The cells are not truly blank, so you'd have to delete the contents in order for them to be ignored in the counts.

 

[CerberaJQ]

Thanks Rory - is there a quick way of doing this? I have tried find and replace with the Find field empty, but tells me there is nothing to replace! What should I be asking it to find?!

 

[RoryA]

You could select your data and then run this code:

Sub CleanData()
    Dim v
    v = Selection.Value
    Dim x As Long
    For x = LBound(v) To UBound(v)
        Dim y As Long
        For y = LBound(v, 2) To UBound(v, 2)
            If Len(v(x, y)) = 0 Then
                v(x, y) = Empty
            End If
        Next y
    Next x
    Selection.Value = v
End Sub

 

[CerberaJQ]

Ahh - perfect, thank you so much!

 

[RoryA]

Glad to help.