Hi all,

Is there any formula which can help me get below result without using single
row filter?
I want to filter 2nd ROW duplicate item + 3rd Row duplicate item.
Please help.

table 1 Table 2
1st 2nd 3rd Result ==== 1st 2nd 3rd Result
1 I a TRUE 1 I a TRUE
1 I b FALSE
2 I c FALSE
2 I a TRUE 2 I a TRUE
3 II a FALSE
4 III a FALSE
5 III b TRUE 5 III b TRUE
6 III b TRUE 6 III b TRUE

I tried to generate table 2 in columns E:H. Try the below formula in cell E2
and copy the same formula to F2,G2,H2. Then copy down as required..

=IF(SUMPRODUCT(($B$2:$B$100=$B2)*($C$2:$C$100=$C2) *($D$2:$D$100=$D2))1,A2,"")


Col A Col B Col C Col D Col E ....
1st 2nd 3rd Result 1st ....
1 I a TRUE =formula
1 I b FALSE
2 I c FALSE
2 I a TRUE
3 II a FALSE
4 III a FALSE
5 III b TRUE
6 III b TRUE

If this post helps click Yes
---------------
Jacob Skaria


"Leonard Chua" wrote:

Hi all,

Is there any formula which can help me get below result without using single
row filter?
I want to filter 2nd ROW duplicate item + 3rd Row duplicate item.
Please help.

table 1 Table 2
1st 2nd 3rd Result ==== 1st 2nd 3rd Result
1 I a TRUE 1 I a TRUE
1 I b FALSE
2 I c FALSE
2 I a TRUE 2 I a TRUE
3 II a FALSE
4 III a FALSE
5 III b TRUE 5 III b TRUE
6 III b TRUE 6 III b TRUE

Hi Jacob,

Thanks for reply but it was not i wanted.
Maybe i have the question asked wrongly.
i have a column of data which more than 1000 and what i want to define is if
the 3rd ROW of the data have duplicate based on the 1st row and 2nd row data.

Currently what i am doing are below:
1. filter item at Col A for item i want
2. sort Col C to assending
3. compare at col D and col E.

Col A Col B Col C Col D Col E ....
1st 2nd 3rd Result
1 I a =exact(A2,A3) =exact(C2,C3)
1 I a
2 I a
2 I b

Then i select False at Col D and TRUE at Col E so i can get below result.

Col A Col B Col C Col D Col E ....
1st 2nd 3rd Result
1 I a False TRUE
2 I a False TRUE

Any idea how what formula is suitable to filter it without step 1 (filter
the item)?

"Jacob Skaria" wrote:

I tried to generate table 2 in columns E:H. Try the below formula in cell E2
and copy the same formula to F2,G2,H2. Then copy down as required..

=IF(SUMPRODUCT(($B$2:$B$100=$B2)*($C$2:$C$100=$C2) *($D$2:$D$100=$D2))1,A2,"")


Col A Col B Col C Col D Col E ....
1st 2nd 3rd Result 1st ....
1 I a TRUE =formula
1 I b FALSE
2 I c FALSE
2 I a TRUE
3 II a FALSE
4 III a FALSE
5 III b TRUE
6 III b TRUE

If this post helps click Yes
---------------
Jacob Skaria


"Leonard Chua" wrote:

Hi all,

Is there any formula which can help me get below result without using single
row filter?
I want to filter 2nd ROW duplicate item + 3rd Row duplicate item.
Please help.

table 1 Table 2
1st 2nd 3rd Result ==== 1st 2nd 3rd Result
1 I a TRUE 1 I a TRUE
1 I b FALSE
2 I c FALSE
2 I a TRUE 2 I a TRUE
3 II a FALSE
4 III a FALSE
5 III b TRUE 5 III b TRUE
6 III b TRUE 6 III b TRUE