help with a min formula

[BORUCH]

hi

i have this formula below

=IF(A2="","",IF((COUNTIFS($A2:A$686,A2)=2)+0=0,FMin(E2:K2),""))

i would like to add one more thing to this, that if its a duplicate it should only show me the fmin of the lowest of the two or three duplicates

the fmin is a custom function the code is below

Function fMin(r As Range)
Dim cel As Range

For Each cel In r
    If InStr(cel.NumberFormat, "$") > 0 Then
        If IsEmpty(fMin) And cel.Value > 0 Then fMin = cel.Value
        If cel.Value < fMin And cel.Value > 0 Then fMin = cel.Value
    End If
Next cel

End Function

example attached

Book2
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	00001	orange	$42.00	10	$38.20	20	$37.20	30	$33.00
3	00002	apple	$42.00	10	$38.20	20	$37.20	30	$33.00
4	00003	banana	$42.00	10	$38.20	20	$37.20	30	$33.00
5
6	00001	orange	$42.00	10	$38.20	20	$37.20	30	$32.00	$32.00
7	00002	apple	$42.00	10	$38.20	20	$37.20	30	$32.00	$32.00
8	00003	banana	$42.00	10	$38.20	20	$37.20	30	$32.00	$32.00
9
10	00001	orange	$42.00	10	$38.20	20	$37.20	30	$43.00
11	00002	apple	$42.00	10	$38.20	20	$37.20	30	$43.00
12	00003	banana	$42.00	10	$38.20	20	$37.20	30	$43.00
Sheet1

Thanks

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER(E2:I100,A2:A100=A2),{1,0,1,0,1}),IF(AND(ROWS(f)>1,A2<>"",MIN(E2,G2,I2)=MIN(f)),MIN(f),""))

You don't need the fMin function,

 

[BORUCH]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER(E2:I100,A2:A100=A2),{1,0,1,0,1}),IF(AND(ROWS(f)>1,A2<>"",MIN(E2,G2,I2)=MIN(f)),MIN(f),""))

You don't need the fMin function,

Hi
it seems to only work if there is a duplicate but if the items is not duplicated it returns blank i want it to return the min like fmin

 

[Eric W]

OK, how about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	4	Peach	42	10	$ 38.20	20	$ 37.20	30	$ 33.00	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	5	kiwi	42	10	$ 38.20	20	$ 37.20	30	$ 43.00	$ 37.20
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER($E$2:$I$100,$A$2:$A$100=A2),{1,0,1,0,1}),IF(OR(A2="",AND(ROWS(f)>1,MIN(E2,G2,I2)>MIN(f))),"",MIN(f)))

 

[BORUCH]

OK, how about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	4	Peach	42	10	$ 38.20	20	$ 37.20	30	$ 33.00	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	5	kiwi	42	10	$ 38.20	20	$ 37.20	30	$ 43.00	$ 37.20
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER($E$2:$I$100,$A$2:$A$100=A2),{1,0,1,0,1}),IF(OR(A2="",AND(ROWS(f)>1,MIN(E2,G2,I2)>MIN(f))),"",MIN(f)))

hi

it works but still one more minor issue if you would have the orange twice or 3 times with the same $32 dollars it should just put it once makes no difference where

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	4	Peach	42	10	$ 38.20	20	$ 37.20	30	$ 33.00	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	5	kiwi	42	10	$ 38.20	20	$ 37.20	30	$ 43.00	$ 37.20
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER($E$2:$I$100,$A$2:$A$100=A2),{1,0,1,0,1}),IF(A2="","",IF(AND(MIN(E2,G2,I2)=MIN(f),COUNTIFS($A$1:$A1,A2,$J$1:$J1,MIN(f))=0),MIN(f),"")))

 

[BORUCH]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item #	Description	Price	qty	Price	qty	Price	qty	Price	results
2	4	Peach	42	10	$ 38.20	20	$ 37.20	30	$ 33.00	$ 33.00
3	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
4	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 33.00
5
6	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
7	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
8	3	banana	42	10	$ 38.20	20	$ 37.20	30	$ 32.00	$ 32.00
9
10	1	orange	42	10	$ 38.20	20	$ 37.20	30	$ 32.00
11	2	apple	42	10	$ 38.20	20	$ 37.20	30	$ 43.00
12	5	kiwi	42	10	$ 38.20	20	$ 37.20	30	$ 43.00	$ 37.20
Sheet7

Cell Formulas
Range	Formula
J2:J12	J2	=LET(f,FILTER(FILTER($E$2:$I$100,$A$2:$A$100=A2),{1,0,1,0,1}),IF(A2="","",IF(AND(MIN(E2,G2,I2)=MIN(f),COUNTIFS($A$1:$A1,A2,$J$1:$J1,MIN(f))=0),MIN(f),"")))

Thank you very much that works perfectly