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

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now