+ Reply to Thread
Results 1 to 12 of 12

Top 3 and Sum balance as Others

  1. #1
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Top 3 and Sum balance as Others

    Hi there,

    Would like to create Top 3 customers and group the remaining customer under "Others" category
    The "Others" category always remain at the last row base on the Location, Type, Category as below :-

    Location Type Category Customer Name Total
    China Payment Category1 Top 1 customer 1800
    Top 2 customer 1000
    Top 3 customer 250
    Others 1500

    Anyone have any ideas?

    Tac
    Attached Files Attached Files

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Top 3 and Sum balance as Others

    I am not sure if i got it, but let's make a try..
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Top 3 and Sum balance as Others

    Hi Tac,

    See the attached file where I have introduced a separate column in pivot data source to achieve the desired result. Thanks.
    HighlightTop3.xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  4. #4
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Re: Top 3 and Sum balance as Others

    Quote Originally Posted by dilipandey View Post
    Hi Tac,

    See the attached file where I have introduced a separate column in pivot data source to achieve the desired result. Thanks.
    Attachment 230468


    Regards,
    DILIPandey
    <click on below * if this helps>
    Hi there,

    Thanks for your reply. My mistake for not able to describe clearly the expected results.
    Attached in doc file is the expected output
    Hopefully you can get a clearer picture.
    Thanks.

    Rgds,
    Tac
    Attached Files Attached Files

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Top 3 and Sum balance as Others

    Hi Tac,

    Below are the rankings which Excel will give, do you agree:-

    Amt - TOP #
    -11,413,966 - Other
    -1,765,677 - 3
    -2,404,776 - Other
    -627,159 - 1
    -1,238,293 - 2


    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Re: Top 3 and Sum balance as Others

    Quote Originally Posted by dilipandey View Post
    Hi Tac,

    Below are the rankings which Excel will give, do you agree:-

    Amt - TOP #
    -11,413,966 - Other
    -1,765,677 - 3
    -2,404,776 - Other
    -627,159 - 1
    -1,238,293 - 2


    Regards,
    DILIPandey
    <click on below * if this helps>
    Hi there,
    The ranking should be the reverse way where the the highest negative value is at the top and the lowest negative value is at the bottom.
    The ranking would be base on the Grand Total and not by individual month.
    Many thanks.


    rgds,
    Tac

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Top 3 and Sum balance as Others

    I am not sure.. as mathematics says highest negative value is on bottom.


    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Re: Top 3 and Sum balance as Others

    Quote Originally Posted by dilipandey View Post
    I am not sure.. as mathematics says highest negative value is on bottom.


    Regards,
    DILIPandey
    <click on below * if this helps>
    totally agree.
    my mistake, should be bottom 3 instead of top 3

  9. #9
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Re: Top 3 and Sum balance as Others

    Quote Originally Posted by dilipandey View Post
    I am not sure.. as mathematics says highest negative value is on bottom.


    Regards,
    DILIPandey
    <click on below * if this helps>
    Hi there,

    Any idea on how to solve this?


    Rgds,
    Tac

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Top 3 and Sum balance as Others

    HI tac,

    See the sheet 1 in the attached workbook

    HighlightTop3(1).xlsx

    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Registered User
    Join Date
    02-15-2005
    Posts
    14

    Re: Top 3 and Sum balance as Others

    Thanks Dili, it works great!!!

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Top 3 and Sum balance as Others

    you are welcome taccat

    cheers

    Please mark this thread as [SOLVED].. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1