I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike

"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

Hi Mike
Your function does not do what I'm after
The result I'm looking for should be according
to sheet K! in my example
--
bill gras


"Mike H" wrote:

Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike

"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

--
bill gras


"bill gras" wrote:

Hi Mike
Your function does not do what I'm after
The result I'm looking for should be according
to sheet K! Column K in my example
--
bill gras


"Mike H" wrote:

Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike

"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

Hi,

I forgot to mention my formula is an ARRAY formula. See below

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and NOT just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"bill gras" wrote:


--
bill gras


"bill gras" wrote:

Hi Mike
Your function does not do what I'm after
The result I'm looking for should be according
to sheet K! Column K in my example
--
bill gras


"Mike H" wrote:

Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike

"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

Hi Mike H
Your formula now works great , but (and most times there is)
could you change the formula to show a blank cell insted of a 0.0

Thank You for your time and effort
--
bill gras


"Mike H" wrote:

Hi,

I forgot to mention my formula is an ARRAY formula. See below

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and NOT just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

"bill gras" wrote:


--
bill gras


"bill gras" wrote:

Hi Mike
Your function does not do what I'm after
The result I'm looking for should be according
to sheet K! Column K in my example
--
bill gras


"Mike H" wrote:

Try this

=MIN(IF(Sheet1!A1:A10=A1,Sheet1!W1:W10))

Where A1 of the active sheet is the name your looking for

Mike

"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

On Sun, 27 Dec 2009 02:30:01 -0800, bill gras
wrote:

Hi Mike
Your function does not do what I'm after
The result I'm looking for should be according
to sheet K! in my example

Try this formula in Cell K1 of sheet K:

=IF(OR(('Sheet R'!A$1:A$15=A1)*
('Sheet R'!W$1:W$15<"")),MIN(IF(('Sheet R'!A$1:A$15=A1)*
('Sheet R'!W$1:W$15<""),'Sheet R'!W$1:W$15)),"")

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

Change the 15 in all places to fit the number of data rows you have in
Sheet R.

Hope this helps / Lars-Åke

When seeing this post I believe that it is very much useful if Microsoft
could have been introduced Largeif & Smallif Functions like Averageif in
2007. I am sure it is not available in 2007 but I dont know whether it is
there in 2010 or not€¦

--------------------
(Ms-Exl-Learner)
--------------------


"bill gras" wrote:

I need a function that will match a name from a range of names
wich include multiple names of the same name , to find the lowest
number or numbers given to a name or multiple names eg:

sheet R! sheet K!
A W A K
1 mary 1 mary 2
2 mary 2 2 john 1
3 john 1 3 bill 2
4 john 4 anne "blank"
5 john 7 5 joe 1
6 bill 6 ivon "blank"
7 bill 5
8 bill 2
9 bill
10 anne
11 anne
12 anne
13 joe 3
14 joe 1
15 ivon

down to 1200 rows all names and numbers are at random sequinces

your help is much appriciated



--
bill gras

Hello Bill,

I suggest to use my UDF Pstat with this small change:
Case "min", "minimum"
If (v(UBound(v))(i, 1) < "" And vR(UBound(v), obj.Item
(s)) v(UBound(v))(i, 1)) Or vR(UBound(v), obj.Item(s)) = "" Then

You can find my UDF he
http://sulprobil.com/html/pstat.html

Regards,
Bernd