+ Reply to Thread
Results 1 to 8 of 8

Find the Highest Manager from 2 columns of Data

  1. #1
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    Find the Highest Manager from 2 columns of Data

    Hello all

    Sorry to just ask a question with no macro even started...........

    In the attched sheet I have a list of employee ID's in column A and the Employee's Line manager ID in Column B. In Column C I need the Line Manager at the top of the pile so to speak. These line managers are listed in column J (J2:J6)

    At the moment I have been writing formulas accross 11 columns (there are 11 possible levels) to check the line managers ID in Column A and see if their line manager is in the top manager list, I do this formula for all 11 columns until the line manager in the list is found. If the Line Manager is in the list I simply repeat it for the next columns. The end result is that in the 11th column all employees will have one of these Line Managers from the Top List in their row.

    Is this possible to do through VBA? I have thought about how I could do this through VBA but I have just hit a brick wall.

    I'm not asking for someone to do all of this for me but if someone could give me a couple of hints around how to look up a value in a list through VBA and if it is even possible to repeat that process per line until the match is found, that would be great.

    Thanks in advance for any hints or tips.

    Regards

    Jim
    Attached Files Attached Files
    Last edited by JimmyA; 02-08-2010 at 07:16 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Find the Highest Manager from 2 columns of Data

    Hello Jim,

    I have not been able to reproduce the results you have mentioned here...
    At the moment I have been writing formulas accross 11 columns (there are 11 possible levels) to check the line managers ID in Column A and see if their line manager is in the top manager list, I do this formula for all 11 columns until the line manager in the list is found. If the Line Manager is in the list I simply repeat it for the next columns. The end result is that in the 11th column all employees will have one of these Line Managers from the Top List in their row.
    There are no formulae in the attached workbook. Not all employees have a top line manager in your list. For example, Employee 111313 has no manager that matches one in your list.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    Re: Find the Highest Manager from 2 columns of Data

    Hi Leith Ross

    Thanks very much for your responce.

    I have changed the employee Id's so it stayed confidential but I think that may have caused some matching problems on this test data as it does not contain all the records.

    I will try and sort it out, add in the formula as an example and re-post.

    Thanks again

    Jim

  4. #4
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    Re: Find the Highest Manager from 2 columns of Data

    Hi Again

    This is a spreadsheet with the formulas that I am currently using. Each column looks for the next line manager. If it matches one of the line managers in column "R" then it will continue to list that until the final column. If it is not in the list it will just give the next line manager.

    I wonder if it would be possible to repeat the check say 10 times in a macro, if by the 10th check it was still not a value in the list then it would just return the highest value found.

    I appreciate that I might not be explaining myself very well but I hope the spreadsheet shows what I am trying to do.

    Thanks again for any hints or tips.

    Regards

    Jim
    Attached Files Attached Files
    Last edited by JimmyA; 02-05-2010 at 04:51 PM.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Find the Highest Manager from 2 columns of Data

    Hello Jim,

    I have a question about the manager ranking. Is it based on where the name appears in the list?

  6. #6
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    Re: Find the Highest Manager from 2 columns of Data

    Hi Leith

    The Managers in Column "R" are not in any order, the first time it finds one of those for an individual that is the manager I want to record in column "C"

    I hope that makes sense.

    Thanks

    Jim

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Find the Highest Manager from 2 columns of Data

    Jim, VBA would be a good idea given recursive nature of the calculation however I would say you could improve the speed (significantly) of your calculation approach should you wish to...

    E2: =B2
    copied down

    F2: =IF(ISNUMBER(MATCH(E2,$R$2:$R$7,0)),E2,INDEX($B:$B,MATCH(E2,$A:$A,0)))
    copied across matrix F2:O4354

    Then your results

    C2: =IF(ISNUMBER(MATCH($O2,$R$2:$R$7,0)),$O2,"No Match")
    copied down to C4354

    I think you will find the above should calculate pretty much instantaneously ...

    For ref. generating a final output per "top LM" of:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-14-2007
    Location
    Chester, England
    MS-Off Ver
    2010 & 2016
    Posts
    312

    Re: Find the Highest Manager from 2 columns of Data

    Hi DonkeyOte

    Thanks very much for your reply.

    I will give that a go.

    Thanks again

    Jim

+ 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