+ Reply to Thread
Results 1 to 7 of 7

Consolidate list and returning corresponding values

  1. #1
    Registered User
    Join Date
    03-22-2015
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    6

    Consolidate list and returning corresponding values

    I have a list of job titles, with corresponding wage rates, there is more than one of each job title, with their respective wage rates.
    I am trying to consolidate the list, to only show one of each job title, with the output of max, min and average wage rates for each title.
    Is this possible?

    In the attached spread sheet, the required output is indicated in red
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Consolidate list and returning corresponding values

    See attached.
    Attached Files Attached Files
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    03-22-2015
    Location
    South Africa
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Consolidate list and returning corresponding values

    Thank you, I have tried the formula in the spread sheet, but it keeps returning a zero value.
    I am battling to attach a spread sheet on the reply, how do I do it?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Consolidate list and returning corresponding values

    mcmahobt Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Consolidate list and returning corresponding values

    Robosheep, welkom aan die forum :0

    Where from in SA and which mine?
    SA expat here from PE and Klerkdorp

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Consolidate list and returning corresponding values

    See if this will help you...
    K
    L
    M
    N
    4
    Artisan Aid
    47.81
    44.58
    46.19
    5
    Assistant Safety Officer
    69.69
    32.63
    43.20
    6
    Banksman
    47.37
    32.98
    41.14


    K4=IFERROR(INDEX($A$4:$A$21,MATCH(0,INDEX(COUNTIF($K$3:K3,$A$4:$A$21),),0)),"")
    L4=IF(K4="","",MAX(IF($A$4:$A$21=$K4,$E$4:$E$21,0)))
    M4=IF(K4="","",MIN(IF($A$4:$A$21=$K4,$E$4:$E$21,0)))
    N4=IF(M4="","",AVERAGEIF($A$4:$A$21,$K4,$E$4:$E$21))

    L4 and M 4 are ARRAY formulas
    ...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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    hen copy all 4 formulas down as needed

  7. #7
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Consolidate list and returning corresponding values

    Quote Originally Posted by FDibbins View Post
    mcmahobt Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread.
    No worries Ford, I should have provided some background.

    Robosheep, to attach a file, you select Go Advanced > Manage Attachments and upload through there. Otherwise, make sure that you are entering the formulas that Ford and I provided (since they are very similar) as array formulas with CTRL+SHIFT+ENTER to confirm.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Returning list values
    By Ron Coderre in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 07:05 AM
  2. Returning list values
    By Ron Coderre in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 AM
  3. [SOLVED] Returning list values
    By bruner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-11-2005, 05:05 PM

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