Large Function listing top 10 values with salesmen names

Discuss the spreadsheet application

Large Function listing top 10 values with salesmen names

Postby dwood505 » Fri Nov 30, 2018 5:40 am

Helo,

I am brand new to this forum and tried to search for my answer, but only found out stuff that I am already familiar with. Here's my problem. I can list the top 10 sales values without the salesperson's names, but I also need the names for the 10 best salespeople. The problem I have is that two of the sales people have sold the exact same amount, but my formula only lists the first salesperson name twice. I used Vlookup and tried using the large function, but got the same results. For some reason it will only list the name of the first salesperson Example below:

Sales Person Sales

John Smith 2,000,000
Kevin Jones 1,970,000
Shirley Watts 1,500,500
Billy Barns 979,500
Bob Friendly 525,000
Bob Friendly 525,000 The second Bob Friendly should be Harold Pierce.

What can I do to fix this issue. Thanks for whatever help you can give me. DWoodly
Open Office Version 12.0.1 (14606.2.104.1.1)
MACOS Mojave version 10.14.1
dwood505
 
Posts: 1
Joined: Fri Nov 30, 2018 5:21 am

Re: Large Function listing top 10 values with salesmen names

Postby FJCC » Fri Nov 30, 2018 6:11 am

Have you considered using a filter instead of a function? To do that, select the data, including the header row and then the menu item Data -> Filter -> AutoFilter. From the drop down menu on the header of the Sales column select Top 10. You can then copy the list to a convenient place.
Alternatively, you can use Data -> Filter -> Standard Filter, set Field Name to Sales, Condition to Largest, Value to 10 and under More Options choose to Copy Results to a convenient place.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7016
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Large Function listing top 10 values with salesmen names

Postby Zizi64 » Fri Nov 30, 2018 8:45 am

Try to combine the LARGE() and the VLOOKUP() functions. The names must be located at the right side of the values for the function VLOOKUP(): Make a helper column with a copy of the names.

Or please upload your ODF type samlpe file here.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7705
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Large Function listing top 10 values with salesmen names

Postby Lupp » Fri Nov 30, 2018 12:07 pm

Try to combine the LARGE() and the VLOOKUP() functions.

This way you get erroneous results as soon as the data LARGE() is applied to, contain any duplicates. Disambiguating them "on the fly" is possible, but a bit tricky. I would suggest to use a sorted copy (option 'Copy sort results to:').
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2310
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Large Function listing top 10 values with salesmen names

Postby Zizi64 » Fri Nov 30, 2018 8:16 pm

Try to combine the LARGE() and the VLOOKUP() functions.

This way you get erroneous results as soon as the data LARGE() is applied to, contain any duplicates.


Yes, it is true.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.0 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7705
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Large Function listing top 10 values with salesmen names

Postby MrProgrammer » Sun Dec 02, 2018 8:37 pm

Hi, and welcome to the forum.

dwood505 wrote:The problem I have is that two of the sales people have sold the exact same amount, but my formula only lists the first salesperson name twice.
[Solved] Need help with INDEX/MATCH function

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3580
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Large Function listing top 10 values with salesmen names

Postby Villeroy » Sun Dec 02, 2018 9:18 pm

A pivot table can do the trick without clumsy formulas.
With a database instead of a spreadsheet it would be even easier.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26263
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Large Function listing top 10 values with salesmen names

Postby robleyd » Mon Dec 03, 2018 12:58 am

Open Office Version 12.0.1 (14606.2.104.1.1)

This is apparently a message from the future ....
Cheers
David
Apache OpenOffice 4.1.6 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2448
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 17 guests