+ Reply to Thread
Results 1 to 14 of 14

Finding the First and Second Largest Number within a Company

  1. #1
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Finding the First and Second Largest Number within a Company

    Hi, can anyone help me on this matter?

    I need to find the first and second largest sum shareholdings (%) for each company. Then, this first and second largest sum shareholdings (%) should gives the type, i.e. the classification of the shareholdings accordingly. I have attached a sample of spreadsheet with some companies. I have more than 7000 data over a 1000 companies. I'm not sure how can I do this. Please help. Thanking you in advance!!

    Regards,
    Mable
    Attached Files Attached Files
    Last edited by mablelim; 10-28-2011 at 08:59 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Urgent help - Finding the First and Second Largest Number within a Company

    I have a very manual solution. See attached. Use the large function for the first and second holding. Move the holding percentage to the left of the type of holding so that you can then use a vlookup.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Urgent help - Finding the First and Second Largest Number within a Company

    Hi Alan,

    Thanks for your advice!
    But I have more than 1200 companies and it will be time consuming to do it manually as each companies might have 2 or 3 or 4 or 5 shareholders and I have to select them one by one......

    If you could or anyone can help further???? Many thanks in advance.

    Regards,
    Mable

  4. #4
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Urgent help - Finding the First and Second Largest Number within a Company

    Hi, I'm still stuck with this problem...anyone can help? Many thanks!!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    Maybe this will work for you.

    See attached example workbook (updated)


    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Finding the First and Second Largest Number within a Company

    Thanks TM, it does work...until I tried to change the max number of rows in the formula and it does not work....can you please tell me how I can change that??? Appreciate your help very much!!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    The formulae are all Array formulae.

    As such, they need to be committed with Ctrl-Shift-Enter rather than just Enter. You only need to change the first row of formulae and then you can copy down as with any other formula.

    Regards

  8. #8
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Finding the First and Second Largest Number within a Company

    Hi TM,
    Thanks for your prompt reply.
    I have tried that and it ok now....but another problem comes.
    Is there any limitation for the maximum number of rows?
    e.g. =IF($B2=$B1,"",LARGE(IF($B$2:$B$310=$B2,$M$2:$M$310),--LEFT(N$1,1)))
    I can only have maximum of 310 rows and if I increase it, the formula will return #N/A. I need to have more rows. How can I solve this??
    Appreciate your help!! Thanks alot!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    Let's be practical here ... I provided a solution to the problem outlined in your sample workbook.

    Now you're asking me to address issues in applying the formulae to a workbook that is cearly a different structure.

    You have two options: 1) you can do on your own ... that is to add columns to the sample workbook so that the data columns line up with the live workbook. Then adjust the number of rows and commit the formulae using Ctrl-Shift-Enter to make sure they still work. If they do, copy the formula from the formula bar (rather than just a straight copy and paste from one workbook to the other) and re-commit.

    2) de-sensitize the data in your live workbook and reduce it to a representative number of rows, maybe 310, and upload it. I'll be happy to try and fix the formula then.

    Regards, TMS

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    =IF($B2=$B1,"",LARGE(IF($B$2:$B$3100=$B2,$M$2:$M$3100),--LEFT(N$1,1)))

    =IF(N2="","",INDEX($D$2:$D$3100,MATCH(N2,IF($C$2:$C$3100=$C2,$M$2:$M$3100),0)))

    Nope, no limit.

    If you don't commit the first formula with Ctrl-Shift-Enter, it may return an answer but, not necessarily, the right answer. The second formula, even if committed with Ctrl-Shift-Enter will return a #N/A

    See the updated example.


    Regards, TMS
    Last edited by TMS; 10-27-2011 at 09:31 AM.

  11. #11
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Finding the First and Second Largest Number within a Company

    Hi TM,
    I have no problem in adding columns but not the rows. Enclosed herewith the workfile with a sample size of 645 observations. I have more than 1200 companies and each companies may have few shareholder's classifications. In fact, I have more than 21000 observations, from year 1996 to year 2009. Hope that you can help. Many thanks in advance.
    Last edited by mablelim; 10-28-2011 at 07:14 AM.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    You have a #N/A in row 311 to which the formulae seems to take exception.

    I have replaced it by "xxxxxx" for the purpose of this exercise.

    There is also a situation that some of the Ticker values only have one entry, hence the formulae for the second entries fail.

    I have adjusted the formulae to cater for this error.

    F2: =IFERROR(IF($B2=$B1,"",LARGE(IF($B$2:$B$645=$B2,$E$2:$E$645),--LEFT(F$1,1))),"*** Not Available ***") (Array Entered)

    G2: =IFERROR(IF(F2="","",INDEX($D$2:$D$645,MATCH(F2,IF($C$2:$C$645=$C2,$E$2:$E$645),0))),"*** Not Available ***") (Array Entered)

    copy across to H2 and I2 then copy down.

    See the example (updated)

    Hope we can put this to bed now ...

    Regards

  13. #13
    Registered User
    Join Date
    10-12-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Finding the First and Second Largest Number within a Company

    Hi TM,
    Thank you so much for your generous help!
    You have solved my problem!!
    Thanks and have a great weekend.
    Regards, Mable
    Last edited by mablelim; 10-28-2011 at 08:58 PM. Reason: Solved

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Finding the First and Second Largest Number within a Company

    You're welcome. Thanks for the rep.

    I'm glad we got there in the end and you have a working solution.

    Youhave a good weekend too.

    Regards, TMS

+ 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