Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2023, 12:56 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default Generating a vertical classification list

Given a list of job applicants who have undergone a selection test
make a vertical list of all the applicants occupying the first 10 positions.
Sample excel attached.
Attached Files
File Type: xlsx SAMPLE-classification-list.XLSX (10.7 KB, 9 views)
Reply With Quote
  #2  
Old 01-24-2023, 02:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Is this some kind of homework?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 01-24-2023, 07:30 AM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Yes! I mentioned "applicants" but is intended for subjects very similar to applicants!
The problem though is exactly the same ...
Reply With Quote
  #4  
Old 01-24-2023, 09:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Are you still using XL 2019?
Have a look at Pivot Table sorting or the LARGE function
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 01-24-2023, 01:56 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Yes, I'm still using xl 2019. As a very limited excel connoisseur, and a from time to time user, I'm not very familiar with PIVOT TABLES and LARGE functions ... I will deepen my knowledge of both functions and and make some attempts ... but I'm afraid this will not be of any help for my need right now! I'm not pretending that someone else does my homework for me ... in a longer o shorter period of time I will be surely able to do it myself ... but presently, if some moral principles do not inhibit it, an immediate and satisfactory help would receive a due appreciation! In any case, whatever the outcome, thank you very much, friends.
Reply With Quote
  #6  
Old 01-25-2023, 08:09 AM
kilroyscarnival kilroyscarnival is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

You may learn better by watching a short video, as I generally do.

https://www.youtube.com/watch?v=1_v-uqoyXqI

This looked pretty straightforward to me.
Reply With Quote
  #7  
Old 01-25-2023, 03:11 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Thank you very much kilroyscarnival ... that was a very great help you gave me! Now, through that video you shared I managed to get more familiar with the italian excel functions INDICE (INDEX) and CONFRONTA (MATCH). Using them I've got some good results but not the exact ones! As a matter of fact there is a significat error the second top mark applicant (the n. 9), see attached sample, is not shown as such! Am I perhaps uncorrectly applying those functions? If so, can I get a tip on how to correct them? Thank you for availability.
Attached Files
File Type: xlsx SAMPLE-classification-list.XLSX (11.2 KB, 4 views)
Reply With Quote
  #8  
Old 01-26-2023, 01:06 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Are you still using XL 2019 or a more recent version?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 01-26-2023, 01:27 AM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Yes, I'm still using the official XL 2019 ...
Reply With Quote
  #10  
Old 01-27-2023, 06:33 AM
kilroyscarnival kilroyscarnival is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Oops, I didn't notice that your list had duplicate entries. That's not going to work with a simple INDEX(MATCH) setup as it will always pull the first match. Also, I think you had a small error in your typed formula; after MATCH on the first row, I think you meant F5, not F6.

Also I just realized that your "problem" statement doesn't require that it be done in a single formula. The most basic way of achieving it would be to copy the applicant and test mark cells to another spot, sort on the test mark column, and delete the ones below the top 10. Another way would be to use a pivot table, and then sort on the Test Mark values column, and filter to the top 10. That might be the easiest way to do it, plus you could make your original data into a table so that when you add rows below they will be included and you only have to refresh. As you can see in the example, I added a rank column (with lots of ties) and the # of times the test is taken (the test mark is an average). Just some ideas.
Attached Files
File Type: xlsx SAMPLE-classification-list-w-pivot.xlsx (16.7 KB, 1 views)
Reply With Quote
  #11  
Old 01-27-2023, 11:26 AM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2019
Novice
Generating a vertical classification list
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Ok, kilroyscarnival, thank for your suggestions. I also thought tha such problem could only be solved with the use of an high level language, the availability and knowledge of which I do not have! Therefore, for the time being, I have to abandon the idea to solve this problem with a single excel formula! Of course personally I will continue to deepen my excel expertise so to try to implement you suggestions, but in this case we both surely agree that an end user can peacefully be called to simply use an excel procedure but not to make a maintenance of it!
So that’s, then, the end of it! Having nevertheless greatly appreciated your availability I duly wish to thank you and thank you very much for everything. A.P.
Reply With Quote
  #12  
Old 01-27-2023, 11:59 AM
kilroyscarnival kilroyscarnival is offline Generating a vertical classification list Windows 10 Generating a vertical classification list Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

There certainly are ways of getting it done just with formulas, but because of the ranking on duplicate (tie) values, it adds some steps. Especially if you don't have the version of Excel that supports dynamic arrays. Here are some discussions of this:

INDEX MATCH function, avoid duplicate returns - Microsoft Community Hub

Additionally, you don't have, strictly speaking, ranks 1 through 10, but as my example showed, a four-way tie for #6, it makes it more complicated when you get to the top ten.

I don't use INDEX/MATCH a lot so I'm not the best person to walk you further through those steps. Best of luck with your learning!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Information Classification Labels ID015 Outlook 0 06-16-2020 08:53 AM
How can I drag/paste multiple links from a website into a vertical Word list (1 link per line)? Thx! JOEL N. Word 1 12-07-2018 02:52 PM
Multilevel List Numbers Vertical Alignment Issue Parting of Ways Word 5 04-07-2016 12:43 AM
Generating a vertical classification list Document classification Ollsbols Word 2 09-17-2014 07:18 AM
Bookmark Classification DrDoom Word 0 06-29-2010 07:08 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft