LOOKUP and Filter Based on Combined Condition

Dear friends, 2019-12-10 15_49_37-Test sheet - Excel.jpg2019-12-10 15_49_02-Test sheet - Excel.jpg

in attached excel sheet image, i need to filter the radiation and active power based on following combine conditions and paste in filter data sheet.

1. filter date & time, active and radiation data which are between radiation 200 to 1000
2. filter date &time, active and radiation data which are below ambient temperature 40 Deg C
3. filter date & time, active and radiation data which are not having negative active power data(Ex. -35)

need formulas and functions to filter and past in other sheet.

Thanks!

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

More detailed instruction:

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Make sure confidential information is removed first!!!!

Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

That came out looking rather larger than I had intended!!

I am a little confused by your request. You have 3 sets of conditions. Does this mean that you need 3 separate sets of results?

I have assumed that your answer was "Yes". Use variants of this:

=IFERROR(INDEX('Raw Data'!A:A,AGGREGATE(15,6,ROW('Raw Data'!$A$3:$A$289)/(('Raw Data'!$D$3:$D$289>=200)*('Raw Data'!$D$3:$D$289<=1000)),ROWS(A$3:A3))),"")

see sheet.

Ignore the above post!!

All 3 conditions, combined.

=IFERROR(INDEX('Raw Data'!A:A,AGGREGATE(15,6,ROW('Raw Data'!$A$3:$A$289)/(('Raw Data'!$D$3:$D$289>=200)*('Raw Data'!$D$3:$D$289<=1000)*('Raw Data'!$E$3:$E$289<40)*('Raw Data'!$C$3:$C$289>0)),ROWS(A$2:A2))),"")

Sure. I'll be back later. Out for a long walk!!

Dear Glenn,

there are 10 more condition in column G to P. in each column cell, i will put value 1 if that condition existing at that time. in column Q i mention output of conditions that is sum of all all conditions. date, time, active power, radiation, temperatures should neglected from filtered data with existing filter conditions.Attachment 653508 respective to the cell(Q column) which has more value 0. so final filtered data should in filter data sheet.

for more clarity, i'm attaching image also for your reference. please help to fix this also.

Thanks for your support in advance.2019-12-11 14_32_32-LOOKUP and FILTER BASED ON COMBINED CONDITION - Reply to Topic.png

Is this what you meant?

=IFERROR(INDEX('Raw Data'!A:A,AGGREGATE(15,6,ROW('Raw Data'!$A$3:$A$289)/(('Raw Data'!$D$3:$D$289>=200)*('Raw Data'!$D$3:$D$289<*('Raw Data'!$C$3:$C$289>0)=1000)*('Raw Data'!$E$3:$E$289<40)*('Raw Data'!$Q$3:$Q$289<>0)),ROWS(A$2:A2))),"")

In that case:

=IFERROR(INDEX('Raw Data'!A:A,AGGREGATE(15,6,ROW('Raw Data'!$A$3:$A$289)/(('Raw Data'!$D$3:$D$289>=200)*('Raw Data'!$D$3:$D$289<*('Raw Data'!$C$3:$C$289>0)=1000)*('Raw Data'!$E$3:$E$289<40)*('Raw Data'!$Q$3:$Q$289=0)),ROWS(A$2:A2))),"")

It's always easier with a small sheet (10-20 rows) and some manually calculated results!!! That's why we put the yellow banner up there....

Dear Glenn,

Thanks a lot. its working well. i need support for following requirement also.

1. is it possible to generate linear curve automatically when data available
2. can we bring the linear regression equation from graph to cell when i entered the date in the summary sheet.

attaching excel sheet for your reference.

this excel sheet has only 20 rows. thanks a lot supporting continuously.

The first thing I did was to copy paste the formula back down the sheet. I modified the formula in G:

=IF(A2="","",C2/31000)

I then set up two Named Ranges (CTRL-F3 to view edit) - Rad and AC_Pwr looking like this:

='Filter Data'!$G$2:INDEX('Filter Data'!$G$2:$G$130,SUMPRODUCT(--(LEN('Filter Data'!$G$2:$G$130)>0)))

I then went to Chart tools/design/select data/Linear regression/Edit

and changed the references to the X and Y data like this:

='Test sheet (2) (1).xlsx'!Rad

It's ESSENTIAL to include the filename!! OK/OK.

That's it, I think... Now you can change some )s to 1s on the raw data and the regression remains correct

I have NOT used your sheet. How can you tell, in advance, that there will ALWAYS be 20 rows of data? The sheet I am using is fully automated, and will adapt to ANYTHING up to 1000 rows of raw data and ANYTHING up to 130 rows of filtered data).

If no date is selected, no data will display. (instead of a blank, the formula reurns #N/A - which the chart will ignore. I used Conditional Formatting to colour the #N/A error the same as the shading in the cell, so you don't see them.

I used 2 Named Ranges (CTRL-F3 to view/edit) to select only those values in D and G that are NUMBERS to use in the calculation of the slope and intercept (presented in two different ways - choose whichever you prefer. If you are going to use the numbers in further formulae, then D2 and E2 will be much easier to use, rather than a messy cell containing numbers and text (B2).

Dear Glenn,

Thanks a lot for new sheet and advising continuously. i can able to see the equation in summary sheet based on date.

there is last concern. if i update the data of 2nd Nov in raw data sheet, filter data and graph is not appearing in filter data sheet. please find image.

sorry if it is already available in the sheet. i don't know how to view the that. could you please help.

thanks a lot for your patience for my stupid questions and seeking help..

The graph that is dsisplayed is for the 2nd November. Change the date in a2 and the graph updates.

It looks the same 'cos you have used the same data.

Which do you prefer? Slope and Intercept separately, or both in one formula?

I am probably going to create 15 sheets, one for each day. This will be significantly quicker than making 15 separate data tables and graphs on the one sheet.

In that case, you can do it. I will explain WHAT to do. But YOU will do it.

What is the MAXIMUM number of datapoints in ONE day?

So, you want 15x1500 = 22,500 rows of data ALL on ONE sheet?????? Navigating round that will be horrible.

And... please stop attaching massive screenshots. this thread is increasingly difficult to follow because of these unnecessary images.

Answer my question YES or NO:

Do you want 15x1500 = 22,500 rows of data ALL on ONE sheet

One final question. Do you actually want to have the graphs, or are you really only interested in the regression equations?

It might be possible to get the equation without 22500 rows of resource-hungry formulae.

Another final question. If you do want all the graphs and formulae, can we restrict it to just columns D and G.

I suspect this sheet will be very, very slow.

This is NOT going to work. You are expecting too much from Excel. With 30,000 rows of raw data it is going to crash. INEVITABLY. EVERY TIME. It will SIMPLY NOT WORK the way you want it to.

This is my LAST offer to you.

1. Calculate the regression formula for all days, over a range of 25,000 rows using a formula. Even this might be IMPOSSIBLY slow.

2. Using a dropdown box, select an individual day. The filtered data and graph will populate. If you want to see another day, select another day from the dropdown. With up to 1500 rows of data coming from 25000 rows of raw data, each time you change the date chosen, there will be about a one minute wait.

If that is not acceptable to you - then you are on your own.

It will not work. My PC crashed setting up the second one. So I can NOT help any further.

I will give this ONE more go. But I will NOT be using your sheet. I will use one from a little earlier. ARE you still planning to have up to 30000 rows of raw data and up to 1500 points on each day?

Please confirm YES or NO.

Hi Glenn,

No. i need for 10 DAYS. That is for 15000 rows

thanks.

I hope that this is close to what you want. DO NOT worry about the size of ranges and so on.

Q1. Will the design work?

Q2. Are there ANY columns in the DayXX sheets that we can get rid of?

Please answer both questions clearly.

Next step. Populate Raw Data with a full set of data. Save as a zip file, or an xlsb, if it is too big and re-post.

Away for 2 hr. I need a full dataset, to see if it is viable. Set first 1500 rows to 1/1/19, the next 1500 to 2/11/19. Same for next 1500. Then copy paste down. Just change the dates, so tou have a full set of 10 days 1500 rows each. Even if they are the same.

Later. One step at a time. Mock up 10 sets of 1500 points first.

Next step. 3 days, each with (potentially) 1500 rows of data. Add any date between 1/1/12 and 12/12/19 ONE AT A TIME in A2:A4. Each date takes 30 seconds to calculate on my 4-year old PC. Does it work OK on your sheet? If so, then we can try to extend it to 5 days and see what happens!

One thought. Is there a possibility of having the raw data for each day in a separate FILE and having one summary FILE drawing in the slope & Intercept??

So. Does it work?

Is there a possibility to divide the data up over multiple files?

Here it is - with 5 sheets. If you want to try it with more, here are the instructions.

Copy Summary A6 & B6 down by ONE row.
Set calculation options to manual.
Click on day 1 tab name. Right click.
Move or copy/move to end (you may need to scroll down to see that)/create a copy.
Rename new sheet Day6.
In B2, amend references to A2 to correct row (a7 for day 6)
CTRL F3, edit the names of the 3 ranges that refer to Day 6 in line with the other ranges - the pattern is obvious.
Close
Summary last row in column B. Amend formula in 3 places to read Day6
Set calculation options to automatic.
Check that the slope/intercept are different from all others (I randomly excluded values all the way down the data).
Repeat as required.