Solved

Adjust script so that entries in the Status field are sorted in reverse-chronological order rather than chronological order

Posted on 2011-03-17
17
304 Views
Last Modified: 2012-05-11
The attached vbscript does a host of things, including concatenating columns (in a csv file), populating new columns, and alpha-sorting entries in one particular column. I need to add functionality to it so that it sort entries in the Status column in rever-chronological order RATHER than crhonological order. How can this be done? I've attached sample input, a supporting file, and the current script. Cheers! 031711Bpm.vbs StateList.txt   myTopic.csv
0
Comment
Question by:GessWurker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35160295
ok, questions

Did you want to sort ALL rows by the column status ONLY?
Did the sort should be over the DATE PART ONLY? or it have to include the rest of the text?
0
 

Author Comment

by:GessWurker
ID: 35160743
No. I'd like to sort content in each cell. Here's a sample of content:

12/01/2010 - PREFILED.;01/10/2011 - INTRODUCED.;01/10/2011 - To HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS.;02/16/2011 - From HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS: Do pass.;02/17/2011 - Passed HOUSE.  *****To SENATE.;02/17/2011 - To SENATE Committee on STATE AGENCIES AND GOVERNMENTAL AFFAIRS.

I'd like it sorted like this instead.

02/17/2011 - To SENATE Committee on STATE AGENCIES AND GOVERNMENTAL AFFAIRS.;02/17/2011 - Passed HOUSE.  *****To SENATE.;02/16/2011 - From HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS: Do pass.;01/10/2011 - To HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS.;12/01/2010 - PREFILED.;01/10/2011 - INTRODUCED.;

Again, the semi-colons are the separators.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35160929
ok, i am attaching the script file with the solution, just be aware of the following:


Since it is ordered by a Date, if there are 2 values for the same date, they will not be ordered ALWAYS exactly as the original, sometimes could be, but other do not since does not have time

The solutions assumes that the Date ALWAYS comes first in the value and with MM/DD/YYYY format

In the  example file STATUS is column U, so i put this column in the script

Please try it
031711pmREVERSE.vbs
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:GessWurker
ID: 35165010
Thanks for the code! Just tested it for the first time, and I'm getting Type mismatch: 'CDate' at line 29
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35166301
Ok, here is a new version that fix that, I assume that some values does not have a Date at first, or like the other column is sending a empty first, any way this version should work.
031711pmREVERSE2.vbs
0
 

Author Comment

by:GessWurker
ID: 35166396
I'm getting a different error now at line 99:

Multiple-step operation generated errors. Check each status.

I'm not sure how to "check each status" though.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35166553
ok, that errors measn a problem with the object that sort the entries, try this version


031711pmREVERSE3.vbs
0
 

Author Comment

by:GessWurker
ID: 35167411
Got the same error on line 99. I have to leave now, but will check in again when I get home ('around midnight tonight, Brooklyn, New York time)
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35170167
Ok, try this one and let me know if throws you an error
031711pmREVERSE4.vbs
0
 

Author Comment

by:GessWurker
ID: 35181723
Ok. No error now. However, no sorting either. Entries are in the original chronological order rather than the reverse-chronological order I'm looking for.

Example (shows no change):
12/01/2010 - PREFILED.;01/10/2011 - INTRODUCED.;01/10/2011 - To HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS.;02/16/2011 - From HOUSE Committee on AGING, CHILDREN AND YOUTH, LEGISLATIVE AND MILITARY AFFAIRS: Do pass.;02/17/2011 - Passed HOUSE.  *****To SENATE.;02/17/2011 - To SENATE Committee on STATE AGENCIES AND GOVERNMENTAL AFFAIRS.
0
 

Author Comment

by:GessWurker
ID: 35181795
Ah...  no wonder. In ...REVERSE4.vbs you removed Function ReverseSortText(strBase)!
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35182884
Yes, I removed because I want to confirm that the ReverseSortText Function was the problem, in a moment I will post you a new version of the function to try on
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35182966
Ok try this one please
031711pmREVERSE5.vbs
0
 

Author Comment

by:GessWurker
ID: 35183393
Hmmm... No error, but still no reverse-sorting either. I tried changing U2 to V2 (since I had to change K2 to L2 for the apha-sorting to work), but still no luck. No reverse-sorting after the script completes.
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35184143
Ok, sorry my bad, here it is the real one :)
031711pmREVERSE6.vbs
0
 

Author Comment

by:GessWurker
ID: 35184443
By jove, you've done it! I had to change "U2" to "V2" but then it worked! THANKS!!!

Points to you!!! (I'd give you a thousand if I could.)
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35184455
:) great! glad to help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question