[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
Medium Priority
?
315 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
Technology Partners: 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!

 

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 2000 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

649 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