Pivot table to find number that is the intersection of set theory

[rbbastos]

Hello Everyone,

I am facing a challenging situation in Excel.
Please visualize the below table. I was wondering if it is possible to create a pivot table that count not only 2019 and 2020 number of interviews. My idea is to make the pivot table to count also the number of person that were interviewed both 2019 AND 2020 (in my example 3). I would love to have it automated since the real spreadsheet has more than 300 rows and it keep increasing.
I am very thankful for any tip on how to solve this challenge. It can even be a formula separated from the pivot table.

Thank you in advance,

 

[steve the fish]

Just create a new column using OR based on the inteviewed column then pivot on that eg

=IF(OR(B2=2019,B2=2020),"19-20","Something else")

 

[rbbastos]

Hi Steve,

Thanks for the quick reply.
The formula you recommended returned true ("19-20") for every evaluation. I believe it is checking only column B. It will return "Something else" only if I include a year different then 2019 and 2020.
I believe we need something that check 2019 and 2020 only if the name appear more then once.

Rbb

 

[rbbastos]

Hi Steve,

Thanks for the quick reply.
The formula you recommended returned true ("19-20") for every evaluation. I believe it is checking only column B. It will return "Something else" only if I include a year different then 2019 and 2020.
I believe we need something that check 2019 and 2020 only if the name appear more then once.

Rbb

I pasted an image of what I am saying to help the understanding. I included some more fake data to show that, even though, "Daniel P. Reese" show up twice, he is not supposed to be counted since he was interviewed 2018-2019 and I want only 2019-2020.

The number 3 that I am trying to include in the pivot table means that I have 3 people (Carla, Catrina and John) that were interviewed both 2019-2020.

Hope this help better.

 

[Michael M]

Maybe this option

Sub MM1()
Dim lr As Long, r As Long, n As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = 0
For r = lr To 2 Step -1
    If Cells(r, 1).Value = Cells(r - 1, 1).Value And Cells(r, 2).Value = "2020" And Cells(r - 1, 2).Value = "2019" Then
        n = n + 1
    End If
Next r
Cells(6, "D").Value = "2019/2020"
Cells(6, "E").Value = n
End Sub

 

[rbbastos]

Maybe this option

Sub MM1()
Dim lr As Long, r As Long, n As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = 0
For r = lr To 2 Step -1
    If Cells(r, 1).Value = Cells(r - 1, 1).Value And Cells(r, 2).Value = "2020" And Cells(r - 1, 2).Value = "2019" Then
        n = n + 1
    End If
Next r
Cells(6, "D").Value = "2019/2020"
Cells(6, "E").Value = n
End Sub

Thanks Michael. That worked. I saved the VBA snipet using a PivotTableUpdate event and now as soon as I refresh the Pivot table it runs the vba code and check if the number is correct.

 

[Michael M]

Excellent....killed 2 birds...?

 

[Michael M]

I guess taking it a step further, you could ask the user for the start and end year via inputBoxes !!

 

[rbbastos]

I guess taking it a step further, you could ask the user for the start and end year via inputBoxes !!

Excellent idea. Gonna implement it.