+ Reply to Thread
Results 1 to 8 of 8

Formula that would provide maximum result between three columns and duplicate information

  1. 05-08-2015, 12:17 PM #1
    egordon
    egordon is offline
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Formula that would provide maximum result between three columns and duplicate information

    Hi,

    Thank you in advance for any help you can provide, I have hit a brick wall trying to figure this out.

    Based on the table below, I would like to isolate one job each person worked on, using the highest number of hours they worked as the qualifier.

    For an example, Aaron Contreras has 1.5 hours on the boiler outage and 10 hours on the log vault 312 manifold... I would need something that would return "Log Vault 312 Manifold", since the highest hours were 10. Is this possible?

    The spreadsheet I'm using has over 1100 employees and some of them worked on 10+ jobs. Something with a conditional format that turns a cell red would be even better.

    Employee Name Hours Description of Work
    Aaron Adrian S 0.5 Unit 3 Outage
    Aaron Adrian S 3 Hunter Boiler Overh
    Aaron Contreras I 1.5 Boiler Outage
    Aaron Contreras I 10 Log Vault 312 Manifold
    Aaron John R 4 OFFSHORE CONSTRUCTION
    Aaron John R 8 Const: HA, HE, HO
    Aaron John R 8 Maint: HA, HE, HO
    Abraham C 4 Onshore Const
    Abraham C 8 Const: HA, HE, HO
    Adam C 8 Fabricate Unit 24
    Adam C 8 Fab 10 ea chem clean hdrs
    Reply With QuoteRegister To Reply
  2. 05-08-2015, 12:50 PM #2
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,031

    Re: Formula that would provide maximum result between three columns and duplicate informat

    Hi there. Is this what you had in mind? This is an Array Formula. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 05-08-2015, 12:53 PM #3
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Formula that would provide maximum result between three columns and duplicate informat

    If you have over 1100 entries, a lookup table may be better suited for your task. See attached for a possibility. Note that it does not take into account ties between number of hours for the same person.
    Attached Files Attached Files
    Spread the love, add to the Rep

    "None of us are as smart as all of us."
    Reply With QuoteRegister To Reply
  4. 05-08-2015, 01:02 PM #4
    egordon
    egordon is offline
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula that would provide maximum result between three columns and duplicate informat

    Thank you both for the quick replies, I believe the second reply with the Vlookup table is closer to what I could use although I'm going to play with both now and will let you know!!!!
    Reply With QuoteRegister To Reply
  5. 05-08-2015, 01:07 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula that would provide maximum result between three columns and duplicate informat

    Use this formula to get generate unique names list:

    In D2:

    =IFERROR(INDEX(A$2:A$12,MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$12),,),)),"")



    and in E2 for Description of Work:

    =INDEX(C$2:C$12,MAX(INDEX((A$2:A$12=D2)*ROW(B$2:B$12)-ROW(A$2)+1,0)))

    Row\Col
    A
    B
    C
    D
    E
    1
    		 Employee Name Hours Description of Work Unique Names Description of Work
    2
    		 Aaron Adrian S
    0.50
    		 Unit 3 Outage Aaron Adrian S Hunter Boiler Overh
    3
    		 Aaron Adrian S
    3.00
    		 Hunter Boiler Overh Aaron Contreras I Log Vault 312 Manifold
    4
    		 Aaron Contreras I
    1.50
    		 Boiler Outage Aaron John R Maint: HA, HE, HO
    5
    		 Aaron Contreras I
    10.00
    		 Log Vault 312 Manifold Abraham C Const: HA, HE, HO
    6
    		 Aaron John R
    4.00
    		 OFFSHORE CONSTRUCTION Adam C Fab 10 ea chem clean hdrs
    7
    		 Aaron John R
    8.00
    		 Const: HA, HE, HO
    8
    		 Aaron John R
    8.00
    		 Maint: HA, HE, HO
    9
    		 Abraham C
    4.00
    		 Onshore Const
    10
    		 Abraham C
    8.00
    		 Const: HA, HE, HO
    11
    		 Adam C
    8.00
    		 Fabricate Unit 24
    12
    		 Adam C
    8.00
    		 Fab 10 ea chem clean hdrs
    Last edited by AlKey; 05-08-2015 at 01:11 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  6. 05-08-2015, 01:36 PM #6
    egordon
    egordon is offline
    Registered User
    Join Date
    05-08-2015
    Location
    Oxnard, CA
    MS-Off Ver
    2007
    Posts
    9

    Re: Formula that would provide maximum result between three columns and duplicate informat

    Wow... that list works exactly perfect. AlKey, thank you so much.

    All three of you are amazing - a million thank you's...

    Erica
    Reply With QuoteRegister To Reply
  7. 05-08-2015, 01:43 PM #7
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,031

    Re: Formula that would provide maximum result between three columns and duplicate informat

    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    Reply With QuoteRegister To Reply
  8. 05-08-2015, 01:52 PM #8
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula that would provide maximum result between three columns and duplicate informat

    You're welcome and thank you for the feedback!
    Last edited by AlKey; 05-08-2015 at 03:43 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Match two columns to two different criteria and provide a result from a third column
    By jbeets in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-04-2015, 09:26 AM
  2. [SOLVED] compare 2 columns provide result in 3rd column
    By snakesbee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2015, 11:34 AM
  3. [SOLVED] Vlook up to find Minimum value and maximum value if value has duplicate result
    By ryan4646 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-08-2014, 09:46 PM
  4. Looking for a formula to provide an outcome from a listing of information
    By s.tara91 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-25-2013, 02:27 AM
  5. Equation to Provide Result Based on 2 Columns
    By Cincy in forum Excel General
    Replies: 3
    Last Post: 02-19-2008, 01:45 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1