Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function won't recognize values unless I type them | Excel Worksheet Functions | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Match formula that pulls unique values from another column? | Excel Discussion (Misc queries) |