10 Replies Latest reply on Sep 13, 2017 4:24 AM by Kalyan Allam

# Help with filtering

Hi!

I have a problem that I just can't figure out and would appreciate any help.

Given these rows:

Row
ID
Product
Sum
11A12
22B43
32A23
42A443
52C32
63B12
71C3

I'd like to build a filter that would only leave rows of IDs that have products A and B. So first find all ID's that have at least one row with product A and at least one row with product B and include ALL the rows of those IDs

In this example I'd like to end up with rows 2,3,4 and 5 since ID 2 is the only one with both products.

Eventually, I'd like to answer questions like what is the total sum of IDs who have product A and B.

Was this unclear enough? Let me know if you need more clarification.

• ###### 1. Re: Help with filtering

Hello Kristian,

What is the expected result.

TG

• ###### 2. Re: Help with filtering

Hi T.G

The expected result would be

Row

ID

Product

Sum

22B43
32A23
42A443
52C32

From this I could, for example, add all sums together. This would answer the question: What is the total sales (Sum) of companies (ID) who have both product A and B

• ###### 3. Re: Help with filtering

Hello,

TG

• ###### 4. Re: Help with filtering

Hi

Problem here is that you are making assumption about the number of rows per IDs. I was unclear about this before but there could be 10 000 rows which have ID = 1 we can't make any assumption about that. Maybe I should have used a bit more exact example.

What I'm looking for, is a method to filter IDs based on Products. My intuition would be the following

1. Make a set of all IDs that have a row containing product A and a row containing product B

2. Use this set to filter out all IDs not included in this set.

However, I'm not able to do this without also filtering away rows that don't include product A or product B e.g in my example above I always end up filtering away row 5.

• ###### 5. Re: Help with filtering

Hi Kritian,

Have not checked the other solution but if you have only 2 products here then a sort of short trick can work.

Give some codes to product A & B

Step 1

A & B=

if [Product]='A' THEN 1000 ELSEIF  [Product]='B' THEN .0001 ELSE 0 END

Step 2

Sum of A & B

{ FIXED [ID]:SUM([A & B])}

Step 3

Here we know that any ID having A & B should have sum value > 1000 with a decimal (.)

Check for=

CONTAINS(STR([Sum of Calc A & B]),'.') and [Sum of Calc A & B] > 1000

NOTE : There can be .0001 adding upto 1000 or say 1000/.0001=10000000 times , I hope you don't have that many entries for a particular ID,

Step 4

Drag ""Check for"" to filter and select TRUE

Thanks,

Ritesh

• ###### 6. Re: Help with filtering

I think sets can be used here. But you need to specify a proper condition for that. Here is my condition for Sets.

I made the sets on IDs .

and for Set B

and then i made a combined Set for IDs containing both A and B.

I am also attaching the workbook. Please let me know if it helps.

Thanks,

Kalyan Allam

• ###### 7. Re: Help with filtering

Thanks! This is exactly what I needed

• ###### 8. Re: Help with filtering

Thank you RItesh! This would have worked also but I found Kalyan's solutions to be more intuitive.

• ###### 9. Re: Help with filtering

I agree

• ###### 10. Re: Help with filtering

you are welcome