Selecting data from large table based on multiple interfering criteria

1. 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.

2. 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.

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.

4. 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. Originally Posted by queuesef
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

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