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

How to sort data using condition depending cell value of different column.

S

sparcle

New Member
Hi, Can anybody help me, my scenario is as below


I have 5 column named as SN, CUST, CAP, ETA, ETB.

i want to calculate & sort ETB column depending on CUST, CAP & ETA column.

[pre]
Code: 
SN                          CUST      CAP        ETA                       ETB

1	                     BEL	YES	15-May-12 02:00              ---
2                            BSL	YES	17-May-12 08:00              ---
3	                     TSL	YES	19-May-12 19:24              ---
4	                     AEL	NO	26-May-12 00:00	             ---
[/pre]
There are 3 condition for calculate ETB column.

1)Is it CAP or not , that is if value of CAP is YES then it got 1st priority.

2)IS it TSL or not, that is if value of CUST is TSL then it got 2nd priority.

3)Third is once supersede can't be supersede second time, i.e in above case asume TSL get 1st priority & move up to the order, then BEL & BSL both supersede by TSL as there ETA is lower than TSL.Now when we calculate again they obviously get 1st priority.


I want to select SN column then when run a macro it will automatic calculate & sort ETB column.


Thanks.
 
Could you post more examples of what you have vs. what you want? Currently, it looks like you could get what you desire by simply sorting descending by ETA, then descending by CUST, and then descending by CAP.
 
Hi, sparcle!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


What do you mean with priorities? An order? If so, it's enough by sortering C descendent, B descendent, and D ascendent?


Regards!
 
it is quick response from members thanks for that.


Sorry for information i provide you, I can't express what i want.So, please go through it again.

[pre]
Code: 
SN      CUST      CAP        ETA                     ETB                  ETC

1        BEL	  YES	  15-May-12 02:00           24-May-12           28-May-12
2        BSL	  NO	  17-May-12 08:00              ---
3        AEL	  NO	  19-May-12 19:24              ---
4	 TSL	  YES	  26-May-12 00:00	       ---
5        BSL	  YES	  29-May-12 08:00              ---

6        AEL	  YES	  31-May-12 19:24              ---
7	 AEL	   NO	  01-June-12 00:00	       ---
8	 TSL	   NO	  04-June-12 00:00	       ---
Assume  we are given 1st row of data. i.e. its  SN is 1, CUST is BEL, CAP is YES, ETA is 15-May-12, ETB is 24-May-12 & ETC is 28-May-12.(ETC is ETB+4)


Now i want to calculate ETB as per condition.

Condition are


1)Is it CAP or not , that is if value of CAP is YES then it got 1st priority.

2)IS it TSL or not, that is if value of CUST is TSL then it got 2nd priority.

3)Third is once supersede can't be supersede second time(i will explain it).


Now i select SN 2,3,4,5 [some cells to select at a time] to calculate ETB.

when calculate manually putting all these condition,


SN 4 will get 1st priority as it fulfill both 1st & 2nd condition.Now it will move to 2nd position in the table after after SN 1 & its SN will be 2 ETB is 20-May-12, ETC is 24-May-12.

(Here both SN 2, BSL & 3, AEL Superseded by SN 4)


Now as per condition 2nd priority goes to SN 5, BSL but due to both SN 2 & 3 Superseded once they can't  Superseded again. So, they will get priority.


Now BSL goes to 3rd position  of table.


Here is table after calculating

SN      CUST      CAP        ETA                     ETB                  ETC

1        BEL	  YES	  15-May-12 02:00           24-May-12            28-May-12
2	 TSL	  YES	  26-May-12 00:00	    28-May-12            01-June-12
3        BSL	  NO	  17-May-12 08:00          05-June-12            09-May-12
4        AEL	  NO	  19-May-12 19:24           09-June-12           13-June-12
5        BSL	  YES	  29-May-12 08:00           13-June-12           17-June-12

6        AEL	  YES	  31-May-12 19:24              ---
7	 AEL	   NO	  01-June-12 00:00	       ---
8	 TSL	   NO	  04-June-12 00:00	       ---
[/pre]
I want to calculate it automatic through macro.

Similarly we take SN 6, 7, 8 & calculate ETB.


Thanks.
 
Hi ,


Thanks for the detailed explanation. What is missing is more data , so that the algorithm can be verified thoroughly.


Is it possible you can upload your file ? Check out the details here :


http://chandoo.org/forums/topic/posting-a-sample-workbook


I have a doubt ; when you are considering the first row , you take it in conjunction with rows 2 through 5. When you are considering the next batch of rows , you are taking only rows 6 , 7 and 8. How do you decide which rows should be combined , since it is 5 the first time and 3 the next ?


Narayan
 
1st row is given. with respect to it i take rows 2 through 5 because these rows are available to me at the time of calculating.Similarly after calculating it rows 6,7,8 are available to me.


Let me know which part is confusing so that i can explain it more.
 
Hi ,


OK. So this means that when you run the macro , it will perform the required calculation on the rows which have already been selected ?


Narayan
 
yes.

will you please tell me how to move up to order the row which get 1st priority after going through the condition mention above.
 
i want to sort the order means move the row up to order on priority basis when it go through the condition as i mention earlier.
 
Hi, sparcle!

Still a doubt, at least: Which is the criteria for assigning values to ETB, ETC, and how do you obtain the calculated dates?

Regards!
 
You must log in or register to reply here.
Back
Top