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

Too Difficult for me to Solve

S

sparcle

New Member
Respected Ninjas,

I started a project two & half month ago based on sorting & also ask your help & get it from ninjas.

But i could not solve it as i have no idea what to do.

So, here i ask it again.


In my uploaded file want a macro or it may be conditional formatting that sort the data.


Data will sort depending on below conditions & only those rows in which Col K has no 'X' value & below selected date value of Col I.


1) If Col D & E has YES value it will sort first, Then if Col D has YES value sort second , Then Col E with YES value sort.


2)In above conditions if there are more than one row satisfying, Then min value in Col G will sort.


3) Rows are sorted below the row which has X value in K col, i.e. in uploaded worksheet row 5 & row 6 will remain still & sorting will occur other than them.


After sorting it looks like sheet2 of my work book.


But the final condition is

4) If a row fulfill conditions & by sorting move up the order then by date value of Col G which it pass these rows are below this row.


Here In my workbook sheet1 by sorting row 9 will move up & it cross two rows with min date value of Col G, they are 7 & row 12.

so they move next to it.


My final sorting format in sheet3.


http://www.2shared.com/file/EVbw8XJE/prob1.html


please guide me to finish it.
 
Hi ,


Let me try and put your logic in my own words :


1. Sorting of data will take place only for those rows which do not have an "X" in column K.


2. Can you confirm whether there will be any row(s) where column I will have dates and column K will not have "X" ? What is to be done in this case , should these rows be included in the sort or should they be excluded from the sort ?


3. When sorting , ensure that all rows which have "YES" in columns D and E are pushed up. Can you confirm whether this pushing up is in row order or date order i.e. if there are several rows , say row 7 , 13 , 17 and 19 , which have "YES" in columns D and E , then all of them will be pushed up ; will the order be the same , or will they be pushed up so that the 4 rows are sorted according to the dates in column G ?


5. Next , if there are any rows which have dates in column G less than any dates in the rows which were pushed up in step 3 , these rows should be pushed up below the rows which have "YES" in columns D and E.


6. Your example shows only one row having "YES" in columns D and E ; if there are many rows fulfilling this criterion , the date criterion should be the minimum date among all these rows. Any rows , whose dates in column G are less than this minimum date , will all be pushed up below the rows pushed up in step 3. Can you confirm this ?


7. Now , the remaining rows will be pushed up , in the following order ; first push up those rows which have "YES" in column D , then push up those rows which have "YES" in column E ; again , you need to confirm whether any date sorting is required , or the rows will be pushed up in the same order in which they occur.


Narayan
 
@ NARAYANK991

Hello sir,


i confirmed you what you ask,


Before that i want to tell something.


In this workbook new rows also added to these existing rows.


1.Sorting of data will take place only for those rows which do not have an "X" in column K & below selected date value of Col I (or you may take it last row with X value, In my worksheet it is I6 as this is the last row with X value.)


So we can split sort data into two condition, those without X value in K col & below date value of Col I sort first then those without X value in K col & above date value of Col I sort secondly. As it may possible there is some row entry in later that have values above Col I value.


But for both of these other condition will same.


It is complicated so i explain it in my way if there is any question regarding this then ask.


i will conform others & explain it shortly.
 
2.Yes sir,

there are columns I with dates and column K will not have "X" . These rows included in the sort .


3. Col A is for reference there is no link in sorting, it may exclude.

if there are several rows , say row 7 , 13 , 17 and 19 , which have "YES" in columns D and E they pushed up so that the 4 rows are sorted according to the dates in column G.


5.Yes


6.Yes sir.


7.Date sorting required.
 
Sir, i explain overall scenario for this & uploaded a document file.

Please go through it.

http://www.2shared.com/file/7awg7ZF-/prob_Sce.html
 
Hi ,


Sorry , but I had completely forgotten about this problem. Can you wait for a day or two ? I'll post something by this evening.


Narayan
 
Hi ,


Sorry , but I have been going through your documented flow chart , and I think nothing less than a week is required to do whatever you have documented ! Can you wait for so long ?


6 days will go in understanding your flowchart ; probably the actual programming may take less than a day !


I really cannot imagine why any process should require this complexity. Are you doing all this manually at present ?


Narayan
 
Hi, sparcle!


I had the best intentions, downloaded your workbook and your document, read it once, then twice, I had to print the 5-pages flow, read it again, tried to simplify, and got lost many times. Despite of being one of the most cumbersome processes I've ever seen (and having worked many years in processes re-engineering it isn't often said, but I doubt about it usefulness), for me it's unintelligible without a complete data set for testing that has:

a) all the possible cases and sequences

b) the desired output, manually written and explained why in each case each row data moved from X to Y row (in the form of: col.n =/<>/<=/</>/>= col. m, with the ands and ors nested as required)


If gonna do so, please do not omit any case/sequence, because the idea is to build a code that tried to logically transform your input data into your output data according to the explanation provided with the output data, using the less as possible the confusing information of your first post and of your document file.


Sorry if it sounds rude or impolite, but as Sir Arthur Conan Doyle said thru Sherlock Holmes, "When you have eliminated the impossible, whatever remains, however improbable, must be the truth.".


Otherwise I think I should adhere to your topic: too difficult for me to solve. And I should wish you the best of the lucks.


Regards!
 
Hello Sir,


@SirJB7

Here i explain it.


http://www.2shared.com/file/dTAkQoaQ/prob2.html


@NARAYANK991


I have no problem at all to wait for getting help from Ninjas.
 
Hi, sparcle!


Several questions.


First from your first uploaded file (prob1):

- I understood that the order (column A values) should be 1/2 ("X" in column K, fixed), then 5/4/3/6 ("Y" in D&E, "Y" in D, "Y" in E ordered by G ascendent), but I supposed that next there'd be placed 8/7 ("N" in D&E ordered by G ascendent), as you said that first criteria is keeping "X" in K untouched, then "Y" in D&E has the first priority, then "Y" in D has the second priority, then "Y" in E has the third priority, and finally within the same priority G ascendent from lower to higher has the fourth priority.

Am I right, or you stated wrongly the priorities?


Now from your kindly explained -I recognize and realize that you made your best efforts preparing this last file (prob2)-:

- Applying the same logic I get why (column A values) 1/2 stay in rows 5:6, why 5 goes to row 7, but then... problems

- why don't 4/10 go to rows 8/9 if they have the second priority ("Y" in D)?

- why don't 3/6 go to rows 10/11 if they have the third priority ("Y" in E)?


Please elaborate a bit more on same file prob2 at worksheet Sheet6, maybe duplicating it as Sheet6Bis. I ask you this so as to not study again a whole new dataset. Thank you.


Regards!
 
@SirJB7


Hello sir,

Thank you for going through this complexity.


In work sheet i explain a term super seed, this is the main reason for all of this.


http://www.2shared.com/file/nJO6jq8b/prob2.html


sir you can also compare this logic to that i have uploaded before in document.

http://www.2shared.com/file/7awg7ZF-/prob_Sce.html
 
Hi ,


I have not done anything on this , since I assumed that SirJB7 is taking it up.


In case he is not , I will go through your specification document and take it up. If I take it up , it is better we correspond by email , since I think your problem is so specific that it may not apply to anyone else. Let us not clutter up this forum with too many posts on this one problem.


My email address is narayank1026@gmail.com


Narayan
 
Hi, sparcle!

Actually haven't had time last week to dedicate to this and this week seems to go thru the same path. If you could wait until next one I'll go on with this issue, otherwise if I were you I'd accept any help from others.

Regards!


@NARAYANK991

Hi!

I was taking it up, but I wouldn't mind if sparcle receives help from anybody else. I'd only suggest him to update the project status here so as to not spend duplicate efforts.

Regards!
 
@ NARAYANK991 & SirJB7

Hello Ninjas,


It is very kind both of you to respond this.


i can wait sir.


Since i clarified some questions arise by SirJB7 recently, it is better to go with him.
 
Hi, sparcle!

Arrived yesterday and planning to work on your issue between today and tomorrow. I've been absent for almost a week.

Regards!
 
Hi,

A couple of suggestions to get responses from a broader group of folks:

1. Some of the folks (including me) are not able to see the files you uploaded (due to firewall restrictions on my end). As such, you may want to consider describing your problem in the post itself

2. You may also want to break your questions into specific challenges you are facing. That might allow some folks to respond to your challenges in between their day-to-day responsibilities. You might then be in a position to tackle your overall problem based on the individual responses.


Cheers,

Sajan.
 
Hi, sparcle!

Tomorrow I'll try to resume (restart in fact) from where we left last month. I apologize for the inattention but I haven't had the time to spend.

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