Dear friend,
Here is a code for me to print the names in ascending order even if values are of same means it is showing one name only instead of the other name
means if values are A=1 B=2 C=3 D=4 E=2
it has to show like this A B E C D
But the result is coming like this A B B C D
Please help me out how to solve it
Formula which i used
Here i am attaching the sample file for your reference=OFFSET(B$6,MATCH(SMALL(C$6:C$18,ROW()-ROW(C$6)+1),C$6:C$18,0)-1,0)test.xls
Do you want the number equivalent of the text in result column? In other words, do you want the name to be encoded as numbers?
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Regards,
Sarang
Col1 : 1 2 3 4 2
Col 2: A B C D E
(Result)Col 3:A B E C D (Required)
Formula:
Please Login or Register to view this content.
By Using this formula result I.e. Col 3 : A B B C D (is Coming)
How to solve this?
it is coming as ABBCD because Match always stops at the first entry
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Any alternate method other than match???
We can help you a lot more if you could post a workbook with more sample data and explain with an example of how you want the output from the input columns
test.xls
Here i attached the file,
i used the result cells by using rank and withour rank formula i need answer in ascending order if same value exist with different names means they have to come in ascending order please help me out.
Originally Posted by maheshkanda
Col1 : 1 2 3 4 2
Col 2: A B C D E
(Result)Col 3:A B E C D (Required)
Formula:Please Login or Register to view this content.
By Using this formula result I.e. Col 3 : A B B C D (is Coming)
How to solve this?From your quotes above, I understand that you need to arrange the names as per the ranking formula you have created.
If that is the case, you can just sort the result after applying the rank. I checked whether there is a possibility if 2 persons will get the same rank, but with your formula you won't get 2 persons with the same rank. However, if you sort the rank column (based on your current formula) they will come one after another (meaning their max value will be either same or in ascending order only).
My question to you is, why do you specifically need to generate the alphabets as in Col 3 as you have shown ?
Last edited by Saarang84; 05-20-2014 at 01:41 AM.
i want to sort with out the rank formula and if there exist two persons means two names has to come in order
Does this mean that both the persons will need to have the same rank or will the ranks be different?
Well
Here is the case first download the attached file.
1) i don't want to use rank function for getting the results.
2) if i used offset with match function means the single name repeating if values are same means i need all names to be present with out repeating a single name.So any alternate method for rank and offset for arranging the names based on the data available.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks