automatically sort data more than certain value when raw data changed

[Benthemtan]

I need to update raw data everyday,my raw data is located at left, everyday it will refresh and i need to extract the data. Which is machine A value higher than 500 will appear on the right or different sheet and Machine B value higher than 50 will appear as well. How should i use formula or function so that machine A and machine B will appear only when the value meet the condition?

Machine A Value Machine A value
Kns1 501. Kns1. 501
Kns2. 700. Kns2. 700
Kns3. 400. Kns5. 800
Kns4. 200. Machine B. Value
Kns5. 800. Sput1. 59
Machine B. Value. Sput3. 100
Sput1. 59. Sput5. 99
Sput2. 40.
Sput3. 100
Sput4. 20
Sput5. 99

Please help and many thanks

[SDCh]

Formula at d2 (array formula - use Ctrl + Shift + Enter):

Please Login or Register  to view this content.

formula at e2:

Please Login or Register  to view this content.

Note :
text with red color = expand as long as you need
text with blue color = criteria


for more information :
http://www.get-digital-help.com/2009...mula-in-excel/

[Glenn Kennedy]

Hi there and welcome to the Forum. It is not easy to get the exact output that you want without using VBA.

This amendment to the above solution will do what you want. It has two advantages. Firstly the solution above will fail if there is any ECP vaue >500. It will also show up in the Cusput results. secondly, it's a non-array (ordinary) formula whic is easier to deal with. see the shet for full details;
Cell D2 and drag down:
=IFERROR(INDEX($A:$A,SMALL(INDEX(((LEFT($A$2:$A$16,6)<>"Cusput")+($B$2:$B$16<500))*10^10+ROW($B$2:$B$16),0),ROW(1:1))),"")
Cell E2: same a s previous solutio.

i have also added the amended forumlae to return the ECP machines and results in columns G & H.

Finally, next time - please attach a workbook and not a picture of a workbook. We have to retype your data before we start. That often puts many people off (me included) from helping!!

[Benthemtan]

Thank for your help

[Glenn Kennedy]

Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.