Excel formula to pull Top 5 -


i trying figure out how pull top (and bottom) 5 items sold particular group, cannot figure out. suggestions?

enter person want check in green box populate fields. 

        paul         jake            john                 steve                jake      salesman    item     amount              paul    blue     202        top 5 items sold     paul    orange   1,500           paul    green    28              paul    pink     62              paul    purple   10,215              paul    cherry   1,616           paul    soda     1,320           paul    tea  1,598      bottom 5 items sold  paul    coffee   22              paul    pop  165             paul    grass    148             paul    weeds    3           paul    tar  147             john    eyes     169             john    teeth    1,258           john    arm  1,247           john    leg  1,396           john    foot     13,598              john    hand     12,458              john    finger   12,477              john    nailfile     1,036           john    brush    4,567           steve   comb     8,901           steve   tweezer  2,345           steve   charger  3,456           steve   plate    4,567           steve   bowl     5,678           steve   cup  6,789           steve   spoon    7,890           steve   knife    8,902           jake    fork     8,998           jake    tongs    1,723           jake    spatula  9,856           jake    cookie jar   1,616           jake    cracker  2,589           jake    cake     1,628           jake    pie  528,116             jake    cupcake  5,286

you can use following formula, assumes

  • your data starts in second row
  • the persons name interested in in d2
  • all names in column a
  • all items in column b
  • all values in column c

  • you type formula cell e1 , drag down there

    {=min(if(((large(if(($a$2:$a$22=$d$2),$c$2:$c$22),rows($a$2:a2)))=$c$2:$c$22)*(countifs($e$1:e1,$b$2:$b$22)=0),$b$2:$b$22))}

for smallest values, replace large small.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more