+ Reply to Thread
Results 1 to 5 of 5

Selecting data from large table based on multiple interfering criteria

  1. #1
    Registered User
    Join Date
    12-02-2016
    Location
    Odense, Denmark
    MS-Off Ver
    Officec 365 Plus
    Posts
    3

    Selecting data from large table based on multiple interfering criteria

    Hi forum users - glad you're up for helping out

    I have a lot of simulation data I need to go through to find an optimum. To do that I need to select/pick/copy/make new sheet with all the rows of data where the reboiler energy consumption is lowest, but I only want one set with lowest reboiler energy per number of stages. My table looks like this:

    Number of stages | Feed stage | Reboiler duty | Reflux ratio |
    30 13 400 2
    30 12 425 2.2
    29 11 430 2
    29 10 450 2.5
    etc.

    The important thing to notice here is that there are multiple rows with the same number of stages (like 30 rows or so per stage), but with varying reboiler duty (and the other values in the row as well).

    I hope you can help me out. I'm hoping there's a solution without the need for VBA code. I could probably write that myself, but it's difficult to teach other not so excel-skilled people

    P.s. The excel-file attached has a sheet of data, RAD1, and a sheet of the result of what I'm asking above in sheet RAD1best.
    Attached Files Attached Files
    Last edited by Gulddrengen; 12-02-2016 at 02:02 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    375

    Re: Selecting data from large table based on multiple interfering criteria

    I'm not sure I understand how you get the lowest, but here's my attempt at it. I used SUBTOTALs to arrange the data and pick the MIN value of ReboilerDuty....you can then use the GROUPS to collapse levels (you may then COPY data to another spreadsheet....Note: you may need to employ Special Paste ~ VISIBLE cells to only grab them and not the hidden levels. When one perspective doesn't work....take another look at it/ a different view.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-02-2016
    Location
    Odense, Denmark
    MS-Off Ver
    Officec 365 Plus
    Posts
    3

    Re: Selecting data from large table based on multiple interfering criteria

    I don't get your method, but by looking over your excel file i realised a very simple solution I can't believe i hadn't thought of before. The trick is to first make a customised sorting using the sort functionality built into Excel so the first priority sort is on the number of stages (ascending or descending doesn't matter) and the secondary is on reboiler duty (ascending). Then make a new sheet with a column of stages (12 to 47 for my dataset) and then use the index and match function in another column: =INDEX('RAD1'!D$2:D$790;MATCH($C2;'RAD1'!$C$2:$C$790;0)). This way I could just drag the new column to find the corresponding values from other columns as well. I experienced that the lookup function didn't search for a match from the top and down (but from somewhere in the middle... WTF??), but the index and match combination did, which I needed to make it work.
    Attached Files Attached Files
    Last edited by Gulddrengen; 12-02-2016 at 02:06 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    375

    Re: Selecting data from large table based on multiple interfering criteria

    Sound like the alternative viewing helped! Cheers! (to explain my method, I used Excels Subtotal function ~ in ver 2010 its on the ribbon under Data, Outline, Subtotal which I added the function 'Min' to the Subtotal....check it out, you may find it useful for something else?

  5. #5
    Registered User
    Join Date
    12-02-2016
    Location
    Odense, Denmark
    MS-Off Ver
    Officec 365 Plus
    Posts
    3
    Quote Originally Posted by queuesef View Post
    Sound like the alternative viewing helped! Cheers! (to explain my method, I used Excels Subtotal function ~ in ver 2010 its on the ribbon under Data, Outline, Subtotal which I added the function 'Min' to the Subtotal....check it out, you may find it useful for something else?
    Yep I will definitely look into it

+ 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] Pull data from a table based on multiple criteria - old formula trying to use AND
    By tweaver in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2016, 04:37 PM
  2. [SOLVED] Selecting minimum based on multiple criteria
    By Vend1301 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-09-2016, 02:03 PM
  3. [SOLVED] Index Match with multiple criteria and selecting data from a large table
    By Aquarock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2014, 09:26 PM
  4. [SOLVED] Help Summing Data from Table Based on Multiple Criteria
    By D. from So Cal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2014, 02:16 PM
  5. Enter data into specific row in data table based on multiple criteria
    By bberger1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 11:04 AM
  6. Selecting Data Based on Multiple Criteria
    By davi2188 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2012, 08:43 AM
  7. Filtering data from one large table into many smaller ones based on certain criteria.
    By hugedomer11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2010, 12:57 AM

Tags for this Thread

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