Macro for ordering dates chronologically in Words Documents

[Silvester]

Hallo Friends.

I know absolutely nothing about macros. Anyway I think I need one to be able to order data in a document.

I have three files/documents with data in them ordered in the usual chronological way from the oldest to the most recent one.

Anyway I want to merge this 3 files into a single document. So I think I need a macro that -once I have copied all the three documents in a new single one- will order those three separates text parts in an unique one with beautiful chronological order from the oldest date data to the most recent date data...

The date in the text is written in this format: D or DD-M or MM-YYYY like in 10-8-2004

Thank You for your very much appreciated help.

Silvano Tironi

[Gintaras]

I afraid that creating such macro will take more time than ordering manually, unless the documents are really large.

I think, it is possible to create such macro, but only if the documents contain only simple text (without any formatting, tables and other objects). We would join all, then parse (using regular expressions) into array, order the array, and join again. hock:

[_pi]

silvester, how is your text formated ?

please post some lines.

i think about reading all three files as string variables and then
to merge the three strings.
make an array of it and convert your date entries in each line
into a format what qm can sort.

...

[silvester]

Thanks for your replays.

Here is 4 lines from one of the 3 documents:

(Sat) 20- 1-1900 21:38 Saturn (Sag) 165° [Gem] Pluto
(Sun) 18- 2-1900 20:56 Saturn (Cap) 162° [Gem] Pluto
(Wed) 23- 5-1900 20:59 Saturn [Cap] 162° (Gem) Pluto
(Mon) 25- 6-1900 18:59 Saturn [Cap] 165° (Gem) Pluto

If you find the solution please tell me step by step what I must do.

Thank You again.

Silvano Tironi

[Gintaras]

The attached file contains macro that does exactly what you need.

[Silvester]

Thank You Gintaras.

I have been quiet busy and Sunday I could not get to the website.

Now what I'm I supposed to do? I downloaded the code into my PC but Windows can't read it.

I'm ready to buy Quick Macros 2 but how I use it in relation with Words Documents to get what I want? I need step by step explanation even for taken for granted actions...

You can write me at my email:

[email protected]


Again thankyou.

Silvano Tironi

[ken gray]

open the QM macro editor, then click "file" then import. then pick that file and it will show up at the end of your macro list.

the instructions about how to use it are in the comments section of the macro itself.

[Silvester]

Thank You for your fruitful advise !

I posted in the "Resources" section my latest need of help from you rocket scientists...

I beg your superior help for two -and lasts- more simple and useful to the masses macros in it.

Best Regards.

Silvano Tironi

[jimbog]

Hi Gindi et al,

1) I have a QM VARIANT array that is 3 columns and 50,000 rows

2) The first column is just a record number (ie, from 1 to 50,000)

3) I need to sort the entire array based on the second column, which is strings of width 0 to 100 characters.

4) Then I will perform an operation to populate the third column. This operation would be based on the strings in the second column.

5) Then I would like to resort the entire array based on the first column (ie, just put it back in record number order).

So....my overarching question is...does this sound doable (I would prefer not to export to Excel and then bring back into QM)...and if so, could you kindly kick me off with a little code?

I can't tell from the code in this post if I must glue all the columns together and sort on fixed positions, or if there is an easier way to work with qsort and arrays...or maybe that is the easy way

If possible, I just need to understand the essence of sorting a multi-column array based on one column. I don't see how to designate one column for qsort to operate on.

Thanks.

[Gintaras]

Example

Code:

Copy      Help

out

;Create test array
ARRAY(VARIANT) a.create(3 12)
int i j; str s ss
ss=
;january
;february
;march
;april
;may
;june
;july
;august
;september
;october
;november
;december
for i 0 a.len
,a[0 i]=i+1
,s.getl(ss -i)
,a[1 i]=s
out "[]Unsorted array:"
for(i 0 a.len) out a[1 i]

;Sort by second column
SortVariantArray2Dim a 1
out "[]Sorted by second column:"
for(i 0 a.len) out a[1 i]

;Sort by first column
SortVariantArray2Dim a 0
out "[]Sorted by first column:"
for(i 0 a.len) out a[1 i]

Function SortVariantArray2Dim

Code:

Copy      Help

;/
function ARRAY(VARIANT)&a col

if(!a.len) ret
if(a.ndim!=2) end "must be 2-dim array"
if(col>=a.len(1) or col<0) end "invalid column index"

int- t_sortArrCol
t_sortArrCol=col
qsort &a[0 0] a.len a.len(1)*sizeof(VARIANT) &__SortVarArr

Function __SortVarArr

Code:

Copy      Help

function[c]# VARIANT*a VARIANT*b

;a and b are pointer-based arays containing 1 row

int- t_sortArrCol
ret a[t_sortArrCol].cmp(b[t_sortArrCol])

[Gintaras]

This version can sort descending. Also, array dimensions don't have to be 0-based.

Function SortVariantArray2Dim

Code:

Copy      Help

;/
function ARRAY(VARIANT)&a col [flags] ;;flags: 1 descending

;Sorts 2-dim ARRAY(VARIANT).

;a - array variable. Not error if empty.
;col - column index (index in first dimension).

;EXAMPLE
;ARRAY(VARIANT) a.create(3 10) ;;3 columns, 10 rows
;;... (populate)
;SortVariantArray2Dim a 1 ;;sort ascending by second column


if(!a.len) ret
if(a.ndim!=2) end "must be 2-dim array"
int lb1=a.lbound(1)
if(col>a.ubound(1) or col<lb1) end "invalid column index"

int- t_sortArrCol
t_sortArrCol=col-lb1
qsort a.psa.pvData a.len a.len(1)*sizeof(VARIANT) iif(flags&1 &__SortVarArrDesc &__SortVarArrAsc)

Function __SortVarArrAsc

Code:

Copy      Help

;/
function[c]# VARIANT*a VARIANT*b

;a and b are pointer-based arays containing 1 row

int- t_sortArrCol
ret a[t_sortArrCol].cmp(b[t_sortArrCol])

Function __SortVarArrDesc

Code:

Copy      Help

;/
function[c]# VARIANT*a VARIANT*b

;a and b are pointer-based arays containing 1 row

int- t_sortArrCol
ret b[t_sortArrCol].cmp(a[t_sortArrCol])

[ken gray]

E-GADS Gintaras!....way to let your 'geek-flag-fly'....sorting Arrays....uggg...i would rather eat glass.

[jimbog]

Wow, Gindi you are the man! Thanks much, will give this a shot.

[jimbog]

Hi Gindi,

These are working great, but I have come up on one issue. My data often has metadata in the first row of the array. I need to exclude this from the sorts. I have a workaround for removing the first row, using the new sort function, then reassembling an array with the metadata in the first row. But I'm not sure how much that will slow down my QM process with large arrays.

Ideally if there is a way to add (without too much trouble on your part) a flag to SortVariantArray2Dim for excluding the first row, that would make this function perfect. At least for now

Jimbog

[jimbog]

Although if there is a way to do an insert into an array (ie, just insert the metadata back into the first row when the sorts are done), I think that would speed my QM process and reduce the need for a new flag. But I don't see a way to do inserts.

[Gintaras]

Code:

Copy      Help

;/
function ARRAY(VARIANT)&a col [flags] ;;flags: 1 descending, 2 exclude first row

;Sorts 2-dim ARRAY(VARIANT).

;a - array variable. Not error if empty.
;col - column index (index in first dimension).

;EXAMPLE
;ARRAY(VARIANT) a.create(3 10) ;;3 columns, 10 rows
;;... (populate)
;SortVariantArray2Dim a 1 ;;sort ascending by second column


if(!a.len) ret
if(a.ndim!=2) end "must be 2-dim array"
int lb1=a.lbound(1)
if(col>a.ubound(1) or col<lb1) end "invalid column index"

int- t_sortArrCol
t_sortArrCol=col-lb1
int sizeofrow=a.len(1)*sizeof(VARIANT)
byte* ptr=a.psa.pvData
int nrows=a.len
if(flags&2) ptr+sizeofrow; nrows-1; if(nrows<1) ret
qsort ptr nrows sizeofrow iif(flags&1 &__SortVarArrDesc &__SortVarArrAsc)

[jimbog]

Thanks Ginid. The functions are working great. One last question. I'm now trying to push one column from the sorted VARIANT array (b) out to a defined range in Excel (f). I'm using the process below, but it takes some time. Might there be a faster way?

for x 0 b.len
f.Cells.Item(x+1)=b[2 x]

[Gintaras]

An excel range can be filled from a 2-dim array using Value property. Dimension roles are different: first dimension is for rows, second for columns.

Code:

Copy      Help

ARRAY(VARIANT) aa.create(b.len 1)
for(i b.lbound b.ubound+1) aa[i 0]=b[2 i]
f.Value=aa

Complete code for testing

Code:

Copy      Help

out

;Create test array
ARRAY(VARIANT) b.create(3 12)
int i j; str s ss
ss=
;january
;february
;march
;april
;may
;june
;july
;august
;september
;october
;november
;december
for i 0 b.len
,b[0 i]=i+1
,s.getl(ss -i)
,b[2 i]=s


ExcelSheet es.Init
Excel.Range f=es.ws.Range(_s.format("C1:C%i" b.len))
;f.Select

ARRAY(VARIANT) aa.create(b.len 1)
for(i b.lbound b.ubound+1) aa[i 0]=b[2 i]
f.Value=aa

[jimbog]

Thanks again Gindi. Everything is working great, especially with smaller arrays. But QM isn't as fast as Excel with bigger arrays. I realize this is likely by design....to keep the QM footprint small. Just curious, is there a way to make my PC give more memory to QM...and is there a way to let QM use it? Or perhaps this is a long and complicated matter....

[Gintaras]

I didn't test the speed of f.Value=aa. If it slower than in Excel scripts, probably the Value function is implemented so that for each array element it switches between QM and Excel processes. That is, the reason of slowlyness is frequent context switches. For example, for this reason acc and htm functions are so slow.

QM, like any other process, can get as much memory as it need. Computer (all programs, not only QM) becomes slow if amount of memory is too small. For example, Windows XP SP2 will run faster with 512 MB or more memory than with 256 MB.

[jimbog]

Thanks for that info. Actually yes, f.Value=aa is very fast. That was a bad post by by me (had been away for a few days).

What I was really asking is if the array sorting functions you created shoud be expected to be slower than Excel. When I sort an array with columns of 50,000 values in Excel, it happens very quickly. But my QM macro doing the same thing takes a bit of time (but it is easier because some of the other operations happen automatically!).

I was curious to know if I should expect QM to be slower. If it should sort at the same speed as Excel then I will look at my code harder to see if something else is slowing it down.

My machine has 2GB of memory.

[Gintaras]

Yes, QM should sort slower. QM code is not compiled to native machine code. Also, calling an user-defined function is quite slow.

I tested sorting array of 46425 elements (all single-word, almost all unique). It took 0.725 s in QM. Function __SortVarArrAsc was called 811736 times. Excel sorted faster, although I cannot measure.