Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default how can we get unique values in match function for same match key.

i picked top10 values with the help of the large formula from an array of
values, on that i wrote match function, with the help of the match function
values i wrote index for getting respective name for the value.its working
propely when the values are not repeated if any value repeated large working
properly but match was giving the first match value only,for that i need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match key.

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an array of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default how can we get unique values in match function for same match

hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an array of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match

No, it doesn't have to be in ascending order. I just demonstrated it like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an array
of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its
working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default how can we get unique values in match function for same match

Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated it like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an array
of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its
working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated it like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an
array
of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its
working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.









  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default how can we get unique values in match function for same match

volko small favour for me, can we do this with out row and rows functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated it like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an
array
of
values, on that i wrote match function, with the help of the match
function
values i wrote index for getting respective name for the value.its
working
propely when the values are not repeated if any value repeated large
working
properly but match was giving the first match value only,for that i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.










  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match

Why do you want to do that?

Basically, the answer would be no but if you explain why there might be some
other formula that might do what you want.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
volko small favour for me, can we do this with out row and rows functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated it
like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an
array
of
values, on that i wrote match function, with the help of the
match
function
values i wrote index for getting respective name for the
value.its
working
propely when the values are not repeated if any value repeated
large
working
properly but match was giving the first match value only,for that
i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.












  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default how can we get unique values in match function for same match

i am using some tool it won't supports the row function,
thats why i am asking.if i am troubling you extreamly sorry.

"T. Valko" wrote:

Why do you want to do that?

Basically, the answer would be no but if you explain why there might be some
other formula that might do what you want.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
volko small favour for me, can we do this with out row and rows functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated it
like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution the
series
must be in descending order. i didn't have the series in any order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from an
array
of
values, on that i wrote match function, with the help of the
match
function
values i wrote index for getting respective name for the
value.its
working
propely when the values are not repeated if any value repeated
large
working
properly but match was giving the first match value only,for that
i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.













  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match

Ok, without using ROW or ROWS...

Assume:

A2:A16 = named range = Sales
B2:B16 = named range = Names
C2:C16 = named range = Ranks

Enter this formula in C2 and copy down to C16:

=RANK(A2,sales)+COUNTIF(A$2:A2,A2)-1

In E2:E11 enter the numbers 1,2,3,4,5,6,7,8,9,10

Enter this formula in F2 and copy down to F11:

=LARGE(sales,E2)

Enter this formula in G2 and copy down to G11:

=INDEX(names,MATCH(SMALL(ranks,E2),ranks,0))



--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
i am using some tool it won't supports the row function,
thats why i am asking.if i am troubling you extreamly sorry.

"T. Valko" wrote:

Why do you want to do that?

Basically, the answer would be no but if you explain why there might be
some
other formula that might do what you want.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
volko small favour for me, can we do this with out row and rows
functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated
it
like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution
the
series
must be in descending order. i didn't have the series in any
order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from
an
array
of
values, on that i wrote match function, with the help of the
match
function
values i wrote index for getting respective name for the
value.its
working
propely when the values are not repeated if any value repeated
large
working
properly but match was giving the first match value only,for
that
i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.

















  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default how can we get unique values in match function for same match

Thanks Volko, thanks for your help
i already written this one but it not supporting by the tool.
for that reason i am searching for alternatives.

"T. Valko" wrote:

Ok, without using ROW or ROWS...

Assume:

A2:A16 = named range = Sales
B2:B16 = named range = Names
C2:C16 = named range = Ranks

Enter this formula in C2 and copy down to C16:

=RANK(A2,sales)+COUNTIF(A$2:A2,A2)-1

In E2:E11 enter the numbers 1,2,3,4,5,6,7,8,9,10

Enter this formula in F2 and copy down to F11:

=LARGE(sales,E2)

Enter this formula in G2 and copy down to G11:

=INDEX(names,MATCH(SMALL(ranks,E2),ranks,0))



--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
i am using some tool it won't supports the row function,
thats why i am asking.if i am troubling you extreamly sorry.

"T. Valko" wrote:

Why do you want to do that?

Basically, the answer would be no but if you explain why there might be
some
other formula that might do what you want.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
volko small favour for me, can we do this with out row and rows
functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just demonstrated
it
like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you solution
the
series
must be in descending order. i didn't have the series in any
order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to be
entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula from
an
array
of
values, on that i wrote match function, with the help of the
match
function
values i wrote index for getting respective name for the
value.its
working
propely when the values are not repeated if any value repeated
large
working
properly but match was giving the first match value only,for
that
i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.
















  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default how can we get unique values in match function for same match

That's some "useful" tool you got there!

Good luck!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks Volko, thanks for your help
i already written this one but it not supporting by the tool.
for that reason i am searching for alternatives.

"T. Valko" wrote:

Ok, without using ROW or ROWS...

Assume:

A2:A16 = named range = Sales
B2:B16 = named range = Names
C2:C16 = named range = Ranks

Enter this formula in C2 and copy down to C16:

=RANK(A2,sales)+COUNTIF(A$2:A2,A2)-1

In E2:E11 enter the numbers 1,2,3,4,5,6,7,8,9,10

Enter this formula in F2 and copy down to F11:

=LARGE(sales,E2)

Enter this formula in G2 and copy down to G11:

=INDEX(names,MATCH(SMALL(ranks,E2),ranks,0))



--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
i am using some tool it won't supports the row function,
thats why i am asking.if i am troubling you extreamly sorry.

"T. Valko" wrote:

Why do you want to do that?

Basically, the answer would be no but if you explain why there might
be
some
other formula that might do what you want.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
volko small favour for me, can we do this with out row and rows
functions.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
Thanks volko,
Thank you very much for your help.

"T. Valko" wrote:

No, it doesn't have to be in ascending order. I just
demonstrated
it
like
that thinking it would be easier to follow.

See this screencap:

http://img413.imageshack.us/img413/1220/file2ww9.jpg

This uses the exact same formula with the data in random order.

--
Biff
Microsoft Excel MVP


"Xcel" wrote in message
...
hi valko thanks for your help,
but i didn't solve my problem exactly, according to you
solution
the
series
must be in descending order. i didn't have the series in any
order
format.please help me.
i was thankful to you forever.

"T. Valko" wrote:

See this screencap:

http://img513.imageshack.us/img513/7177/file1lw1.jpg

The formula used is an array formula. Array formulas need to
be
entered
using the key combination of CTRL,SHIFT,ENTER (not just
ENTER).

--
Biff
Microsoft Excel MVP


"Xcel" <Xcel @discussions.microsoft.com wrote in message
...
i picked top10 values with the help of the large formula
from
an
array
of
values, on that i wrote match function, with the help of
the
match
function
values i wrote index for getting respective name for the
value.its
working
propely when the values are not repeated if any value
repeated
large
working
properly but match was giving the first match value
only,for
that
i
need
individual match values. pls anyone help me.
1 san
10 raj
9 cami
12 jhon

Regards,
Santhosh.


















Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match function won't recognize values unless I type them bdp1978 Excel Worksheet Functions 5 April 30th 23 07:43 PM
Using Match function with duplicate values in an array Richard Excel Worksheet Functions 3 April 22nd 23 07:45 PM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
Match as well as does not match array function Vikram Dhemare Excel Discussion (Misc queries) 7 April 25th 06 09:15 AM
Match formula that pulls unique values from another column? alehm Excel Discussion (Misc queries) 6 September 8th 05 10:38 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"