+ Reply to Thread
Results 1 to 4 of 4

Sort values into one table.

  1. #1
    Registered User
    Join Date
    05-10-2021
    Location
    Minnesota,USA
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    2

    Sort values into one table.

    Hi all,

    I have a spreadsheet that lists out the materials (and its compounds) that go into a master product

    What I want is to have the spreadsheet automatically fill in the low and high range values of each compound by part number for the master product (listed in green on excel), based on the material compounds listed (compounds from Materials #1,Materials #2,etc) Example: I want to find what the lowest range and highest range number of Part Number 1111 for all the materials listed below the green area, which would be 0 and 100 respectively in this case.

    It's easy enough to do this manually in the example but what if I had potentially 50 materials that could go into the master product?

    Can someone provide some guidance on how to proceed?

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 365
    Posts
    184

    Re: Sort values into one table.

    There seems to be some math missing in your spreadsheet

    the "Database says 1111 the highest range is 50, but the green table says it is 100... this is confusing or there may be some calculations missing on how to get the "high range".

    Let me see if I under stand what you are asking and see if you can fill in the blanks.
    Material 1 - 4 is a table of values material 1 can have Part number 1111 have high range equal 50 but in material 4, the high range is 100, do I have that right?

    If that is the case then in the Overall compound table how do you know what "Material table" are you looking up... row 2, what made you choose Material 4 over Material 1?

  3. #3
    Registered User
    Join Date
    05-10-2021
    Location
    Minnesota,USA
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    2

    Re: Sort values into one table.

    Thanks for the quick reply,

    To answer "Database says 1111 the highest range is 50, but the green table says it is 100... this is confusing or there may be some calculations missing on how to get the "high range",

    The green table says the highest range for part 1111 is 1000 because the high range of material 4 is 100.

    Material 1 - 4 is a table of values material 1 can have Part number 1111 have high range equal 50 but in material 4, the high range is 100, do I have that right?
    Yes, material 1 can have a high range value of 50 and material 4 can have a range anyone from 0 - 100 (with 100 being the high range).

    f that is the case then in the Overall compound table how do you know what "Material table" are you looking up... row 2, what made you choose Material 4 over Material 1?

    I guess in my case, material 4 was chosen because it has the greatest number for the high range. I simply want to know what will be the highest/lowest range for each of the compounds in the overall compound table regardless of which material it came from.

    Does that clarify what I am asking? Thank you!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Sort values into one table.

    Perhaps the following will do what you want:
    1. Paste the following into cell C5 and then drag the fill handle down to cell C10: =MINIFS(C$15:C$39,A$15:A$39,A3)
    2. Paste the following into cell D5 and then drag the fill handle down to cell D10: =MAXIFS(D$15:D$39,A$15:A$39,A3)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Sort a table with its values accordingly to another column
    By axcelenator in forum Excel General
    Replies: 2
    Last Post: 12-24-2018, 05:04 AM
  2. Pivot table - sort two column values using 2 different sort values
    By Jacks18928 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2018, 07:38 AM
  3. Pivot Table/Chart - sort by values
    By bpc637 in forum Excel General
    Replies: 5
    Last Post: 05-01-2017, 04:53 PM
  4. [SOLVED] Using a pivot table to sort on values only
    By julesmctavish in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-20-2016, 02:02 AM
  5. [SOLVED] VBA to sort a table for values between
    By JR Montana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-19-2014, 11:32 PM
  6. [SOLVED] Sort values in Table
    By dealer in forum Excel General
    Replies: 3
    Last Post: 06-08-2014, 07:00 AM
  7. How to sort out values and its doubles from a table
    By drzeto in forum Excel General
    Replies: 1
    Last Post: 03-21-2013, 01:24 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