• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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

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
GessWurker
Asked:
GessWurker
  • 9
  • 8
1 Solution
 
gamarrojgqCommented:
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
 
GessWurkerAuthor Commented:
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
 
gamarrojgqCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GessWurkerAuthor Commented:
Thanks for the code! Just tested it for the first time, and I'm getting Type mismatch: 'CDate' at line 29
0
 
gamarrojgqCommented:
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
 
GessWurkerAuthor Commented:
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
 
gamarrojgqCommented:
ok, that errors measn a problem with the object that sort the entries, try this version


031711pmREVERSE3.vbs
0
 
GessWurkerAuthor Commented:
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
 
gamarrojgqCommented:
Ok, try this one and let me know if throws you an error
031711pmREVERSE4.vbs
0
 
GessWurkerAuthor Commented:
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
 
GessWurkerAuthor Commented:
Ah...  no wonder. In ...REVERSE4.vbs you removed Function ReverseSortText(strBase)!
0
 
gamarrojgqCommented:
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
 
gamarrojgqCommented:
Ok try this one please
031711pmREVERSE5.vbs
0
 
GessWurkerAuthor Commented:
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
 
gamarrojgqCommented:
Ok, sorry my bad, here it is the real one :)
031711pmREVERSE6.vbs
0
 
GessWurkerAuthor Commented:
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
 
gamarrojgqCommented:
:) 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.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now