• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting of Data based on critera

khelgadi

khelgadi

Member
Dear All,

Please go through the attached sample file.


https://docs.google.com/file/d/0B0W5ZzWOyj6TV0ZpYnBNVHNmNUE/edit?usp=sharing


The Data sheet contains the process steps while result sheet shows the batches ( in process) at every dept.

I have tried using rank command, but failed to do so. You Excel Gurus are requested to help me out.

Thanks
 
Hi khelgadi,


Thanks for the permision, Can you explain the process how the desired results are obtained and what the rule that will apply. Is this interpretation of your process correct:

[pre]
Code: 
Sr.	Cleaning 	Drying	        Packing	        FG Clearance	Qty
1	25-Feb-13	03-Mar-13	18-Mar-13	20-Mar-13	16
2	26-Feb-13	UP	        Due	        Due	        21
3	01-Mar-13	12-Mar-13	14-Mar-13	21-Mar-13	28
4	01-Mar-13	13-Mar-13	16-Mar-13	UP	        15
5	04-Mar-13	UP	        Due	        Due	        27
6	04-Mar-13	UP	        Due	        Due	        25
7	04-Mar-13	27-Mar-13	UP	        Due	        29
8	05-Mar-13	10-Mar-13	19-Mar-13	21-Mar-13	15
9	06-Mar-13	UP	        Due	        Due	        11
10	07-Mar-13	12-Mar-13	19-Mar-13	UP	        17
11	09-Mar-13	13-Mar-13	UP	        Due	        18
12	09-Mar-13	13-Mar-13	UP	        Due	        20
13	10-Mar-13	14-Apr-13	20-Mar-13	UP	        21

..Where UP = Under Process & Due = Process is Due
[/pre]

???


Regards,
 
Dear Faseeh,

The interpretation is correct.

The results shall show list of "UP" Items.

Even I was not able to put it this way to explain you. I have posted here only required columns as the actual data sheet contains almost 32 of them!

Thanks for replying.
 
Hi ,


I am not sure I have understood your first set of results , since it is not consistent with the remaining sets ; can you check your file here ?


https://docs.google.com/file/d/0B0KMpuzr3MTVWGlNZTlfUmYwYTQ/edit?usp=sharing


Narayan
 
Hi Khelgadi,


With Narayan's solution already in hand, you can also try these formulas, i only need to be adjusted for the INDEX() Formula:


Code: 
=INDEX($B$5:$B$17,SMALL(IF($E$5:$E$17="UP",ROW($E$5:$E$17)-4),ROW(A1)),0)

[code]=INDEX($C$5:$C$17,SMALL(IF($E$5:$E$17="UP",ROW($E$5:$E$17)-4),ROW(A1)),0)

=INDEX($H$5:$H$17,SMALL(IF($E$5:$E$17="UP",ROW($E$5:$E$17)-4),ROW(A1)),0)


Press Ctrl+Shift+Enter to execute. It will give you following:

[pre]2 Bhrungraj Ghana 21
5 Nirgundi Ghana 27
6 Guggul Shodhan 25
9 Kadunimbapatra Ghana 11[/code][/pre]
Regards,
 
@ Narayank991 : Thanks Dear! The first set of results were correct from point of view and the next columns did work....

@ Faseeh: Thanks to you ! Your solution was also perfect!


I combined the solution provided by both of you to resolve the prob! Thanks both of you. I was trying hard to do this since couple of days.

Chandoo.org Rocks!
 
You must log in or register to reply here.
Back
Top