Filter excel pivot table by complex combination of several fields

Question

Let's assume I have an excel table with at least 3 columns, "Text1", "Text2" and "Number". Text1 and Text2 obviously contain textual content and Number is a numerical value. I create a pivot table from this table with Text1, Number and Text2 in the rows (in this order). I do already group my results by ranges of Number.

I would like to filter my pivot table so that I only include results, that share a value of Text1 with at least one other entry, while also being the same range of Number and while having a different value for Text2 than the other entry (the one they share Text1 with).

Is this possible? I don't mind adding additional fields to the original data table for calculations if necessary.

Example

Let's say I have a table that contains the fields FirstName, Sex and Age like this:

FirstName    Age    Sex
Kim          19     Female
Kim          57     Female
Kim          20     Male
Tom          23     Male

I group my ages by steps of 25, so 0-24, 25-49 and so on. In this case I would like to only find all the Kims in the same age range that have a different sex.

Answer 1

Thanks to Ilia's comment, I was able to find a solution. I needed three additional columns in my table:

1. Concat: The concatenation of my Text1 and Text2 field
2. Group: A field that calculates the groups of my Number field (using QUOTIENT)
3. Count: This field uses COUNTIFS to count my fields if there are other matches in the Concat column that have the same value as this has in the Concat column and the same for my Group column.

I am now able to use my Count column as a row in the pivot table (below Text2) and add a label filter to check if the value is greater or equal to 2.