How to Return Partial List of Column Values when Values in Other Columns meet a Condition

**finalmike** · 08-03-2018, 10:45 PM

Well, I am very stumped on this one. I have been building a spreadsheet to sort through data files and it's been going ok until now. I have tried everything but to no avail.

The problem:

Basically, as will be more clear when viewing the accompanying spreadsheet, I want to collect time and H data starting when cycle=6000, displacement <0.001, but after the 6000th cycle completes. In other words, there is a brief time period before displacement ramps up and down for the last time where force<0.01 and cycle=6000. I want to start at row 258 not row 174.

Thanks a lot in advance for anyone who can help me solve this very frustrating problem. I am not a programmer, but I think it's very satisfying building spreadsheets and macros that perform repetitive analyses and tasks for you.

Best!

**Kamal Asharaf** · 08-04-2018, 12:45 AM

Dear Mike,

Please check if attached pivot table helps you... you may filter as per your requirements.

Actually not able to understand the exact requirement. There is no difference at row 258 not row 174 for cycle and Displacement.

More elaboration will help.

Regards,

Kamal

**finalmike** · 08-04-2018, 02:00 AM

I attached a graph to show what I mean. I could do this all manually of course, but I'd like to have a formula that can auto populate some cells so I can graph them.

**JeteMc** · 08-07-2018, 10:14 AM

Perhaps this will help.
I added a helper column (Graph) which may be moved and/or hidden for aesthetic purposes. The helper column is populated using:

Formula:

=AND(B2=6000,C2=0,D2<0.001,MAX(E2:E$1000)=E2)

Note that after working on this it occurred to me that you can get the same results using (Graph2): =MAX(C2:C$1000)=0
In the table "Time vs. H after final cycle?" the Time column is populated using:

Formula:

=INDEX(A$2:A$1000,AGGREGATE(15,6,(ROW($2:$1000)-1)/($F$2:$F$1000=TRUE),ROW(1:1)))

The H Value column is populated using:

Formula:

=INDEX(E$2:E$1000,AGGREGATE(15,6,(ROW($2:$1000)-1)/($F$2:$F$1000=TRUE),ROW(1:1)))

Note that I did not do anything to the graph.
Let us know if you have any questions.

How to Return Partial List of Column Values when Values in Other Columns meet a Condition

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to Return Partial List of Column Values when Values in Other Columns meet a Condition

Re: How to Return Partial List of Column Values when Values in Other Columns meet a Condit

Re: How to Return Partial List of Column Values when Values in Other Columns meet a Condit

Re: How to Return Partial List of Column Values when Values in Other Columns meet a Condit

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to create list based on retrieved values that meet a condition

[SOLVED] If condition met, return all values from column

Swap values between 2 columns when meet criteria on the 3rd column

[SOLVED] Lookup partial values and if match return partial value from another cell

Return top values that meet a certain condition

Return any values from partial text in single reference column

looking up two values in two columns and return the values in the third column

Bookmarks

Bookmarks

Posting Permissions