Filter option on multiple columns with condition

[rex759]

The goal is to filter out rows to exclude duplicate Model numbers that have a total Variance of zero.

For example, in the range below, Locations 1 & 2 have the same Model number and the total Variance is zero. I want to filter or exclude them from the sample.
Rows 3, 4 & 5 have the same model number but only rows 3 & 5 has a total zero variance. I would exclude 3 & 5 but keep row 4.

The yellow highlighted rows are the ones I want to exclude

The results can be displayed in another column, filtered in place or just highlight rows that meet the criteria.

I can set up the sheet with helper columns as needed. I just don’t know where to begin.

Any advice is appreciated

current_discrepancies_202308120305.xlsx
	A	B	C
1	Location	Model	Variance
2	1	111	26
3	2	111	-26
4	3	222	18
5	4	222	15
6	5	222	-18
7	6	333	60
8	7	444	345
9	8	444	-345
10	9	555	50
11	10	666	67
Sheet1

 

[rondeondo]

Hi Rex,
You may be able to achieve what you're after with a sumif as a helper
so in column D, starting at D2 put

=SUMIF(B:B,B2,C:C)

then in column E, starting at E2

=IF(D2=C2,"Keep","Remove")

Copy those down and you'll have "Keep" next to the rows you have specified.
This will fall over if you have more complex number in the variance column.
Might be a start anyway, it works for your sample

 

[Anonymous1378]

Try this, perhaps? It assumes there won't be 26, -26 and 26 again for a particular model, which might be a terrible assumption.

Book13
	A	B	C	D	E	F	G
1	Location	Model	Variance
2	1	111	26		4	222	15
3	2	111	-26		6	333	60
4	3	222	18		9	555	50
5	4	222	15		10	666	67
6	5	222	-18
7	6	333	60
8	7	444	345
9	8	444	-345
10	9	555	50
11	10	666	67
Sheet1

Cell Formulas
Range	Formula
E2:G5	E2	=FILTER(A2:C11,ISNUMBER(XMATCH(B2:B11&C2:C11,INDEX(UNIQUE(ABS(B2:C11),,1),0,1)&INDEX(UNIQUE(ABS(B2:C11),,1),0,2))))
Dynamic array formulas.

 

[rex759]

Thank you for the replies. I apologized that I didn't realized the last row of my chart did not post. Location 11 has another entry of the same model number and the same variance. Since the sum of the variance for model 10 & 11 is not zero, I would want to keep both of those entries. Rondeondo's solution is close. I do like using the filter option but couldn't get it to work either. Your statement about numbers repeating can happen so I don't think the "Unique" option would work.

current_discrepancies_202308120305.xlsx
	A	B	C
1	Location	Model	Variance
2	1	111	26
3	2	111	-26
4	3	222	18
5	4	222	15
6	5	222	-18
7	6	333	60
8	7	444	345
9	8	444	-345
10	9	555	50
11	10	666	67
12	11	666	67
Sheet1

 

[rex759]

I came up with this formula ; IF(SUMIFS($C$2:$C$21,$B$2:$B$21,B2)=0,"Remove",""). Again, this is close to working but not quite. It doesn't calculate Locations 3 & 5 correctly. Those should be removed but it doesn't catch them I think because there are three occurrences of model # "222". Even though the 18s cancelling each other out, the 15 is higher than zero so it doesn't flag it. I am not sure how to get around this or if someone has a better solution. Thanks in advance.

 

[rex759]

Hello again,

I am not sure if I should start a new post but I am sure someone will tell me.

I was able to get the correct results if there are two duplicate model numbers
If there are more than two model numbers, this is where I am stuck.

Column E has the correct results.
Column F is where I started creating a formula but could not get it to work. How do you compare combinations of individual entries of the same model number?
For model # 777, there are 6 occurrences. The (location 12) -950.40 and (Location 15) 950.40 =0 so that should be removed.
The (location 13) 475.20 + (location 17) 2376=2851.20. So either location 14 or location 16 with -2851.20 can be removed.

Any help/advice is appreciated.

current_discrepancies_202308120305.xlsx
	A	B	C	D	E	F
1	Location	Model	Variance	If ony 2 dup Model# (This is working)	If more than 2 Model# Desired Results	Not working
2	1	111	26	Remove
3	2	111	-26	Remove
4	3	222	18		Remove	15
5	4	222	15			15
6	5	222	-18		Remove	15
7	6	333	60
8	7	444	345	Remove
9	8	444	-345	Remove
10	9	555	50
11	10	666	67
12	11	666	67
13	12	777	-950.4		Remove	-2851.2
14	13	777	475.2		Remove	-2851.2
15	14	777	-2851.2		Remove	-2851.2
16	15	777	950.4		Remove	-2851.2
17	16	777	-2851.2			-2851.2
18	17	777	2376		Remove	-2851.2
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF(COUNTIF($B$2:$B$18,B2)=2,IF(SUMIFS($C$2:$C$18,$B$2:$B$18,B2)=0,"Remove",""),"")
F2:F18	F2	=IF(COUNTIF($B$2:$B$18,B2)>2,SUMIF($B$2:$B$18,B2,$C$2:$C$18),"")

 

[rex759]

I came up with a different approach since I couldnt get what I originally wanted through VBA.