Sorting problem in Lotus 123 2

[syl93]

I have a set of data consisting of 4 columns where the data in the first 3 columns are the same, and the data in the last column starts at 11 and goes up to 35.
For example:
A B C D
5 4,106.21 1 0 11
6 4,106.21 1 0 12
7 4,106.21 1 0 13
8 4,106.21 1 0 14
...

The macro that sorts the data is:
'/DSRDA:A5.A29~PA:A5~A~G

When I performed the sort with this set of data, I did not expect it to actually do anything (i.e. the data would remain exactly the same) since it is sorting by the first column and all the values in the first column are equal.
That is the result when I do this same sort in Excel, but not so in Lotus 123. When I run the macro, the values in the 4th column are rearranged.

Can anyone explain why this is happening?

Any help will be appreciated.

 

[DaleWatson123321]

Hi syl93,

Sure, ...be glad to help...

I strongly suspect (expect) that the numbers that "look" identical are actually "NOT" - i.e. they are "formatted" to show what you see, but the "actual" values in the cells likely contain "trailing" decimals that you don't see simply because of the formatting.

To check out the actual "full" number in the cells, you should format the cells as "Automatic", or "General".

One additional, relevant piece of information that you might already be aware of is that Lotus 123, starting with version 3 (PRE-WINDOWS VINTAGE - REALLY OLD already by comparison with today's Excel), can VASTLY outperform Excel in sorting - with "multiple" extra sorting fields. Just now I tested up to 20 extra sort fields with no problem. I'm not aware of what the limit might be, but 20 seems more than adequate.

Excel, on the other hand has a MUCH LOWER LIMIT. I have Excel '97, and it can only sort with 3 sort fields.

So, "what ever happened to Excel ? ? ?"

Does anyone know whether Excel 2000 has any more sorting capability ?

I hope this information proves useful.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[syl93]

That was what I suspected at first as well, but I looked at the numbers out to the 15 decimal places that Lotus 123 will display, and they were still identical.

By the way, as far as I can tell in Excel 2000, you can still only sort with 3 fields. You might be able to sort with more fields using a macro, but I haven't looked into that.

 

[syl93]

I just wrote a set of if statements to further check if all the numbers in the first column were indeed identical, and it confirmed that they are definitely equal.

 

[DaleWatson123321]

syl93,

Thanks for the feedback.

If you still want to get to the bottom of this (weird) situation, you could, I suppose, start with what I did... enter the just the numbers you presented here, into a separate sheet, and then sort.

I can confirm that I had NO problem with these numbers in BOTH Lotus 123 versions 3.4 and 5.0. Therefore, if you DO have a problem after doing exactly what I did, then perhaps there's a problem with the version of Lotus 123 you have.

Actually, I just thought of an option that SHOULD DEFINITELY WORK...

First the "explanation"... While the numbers you see (even to 15 decimals) still show the same, because of the "hexidicimal method" used by spreadsheets to store numbers "internally", the "actual" number is different than the number shown - even to 15 decimals. I had this explained to me some years ago, and I can't recall the exact explanation, but I DO recall the "solution"...

Solution... Use the ROUND function.

1) In adjacent columns, use the ROUND function and reference the existing numbers. In E5, for example, enter @ROUND(a5,2). In F5, enter @ROUND(b5,0). In G5, enter @ROUND(c5,0). In H5, enter @ROUND(d5,0).

2) Then copy E5..G5 down for the rest of the rows.

3) Convert the formulas to values.

4) Cut and Paste the formulas to replace the existing numbers.

This HAS to work. Please advise as to how you make out.

For anyone who creates formulas (Excel or Lotus) that can cause a trailing decimal - for example by using DIVIDE, a GOOD practice to use is to ALWAYS include the ROUND function - to prevent situations "just like this one".

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[syl93]

Thanks for all your help on this.
I am thinking that it must be my version of Lotus 123. I am using Release 9.7.

I tried the round function, and it still has the same problem. In the process of doing that, I noticed something else that is weird - I copied the 4 columns to another part of the worksheet (with the rounded numbers in the first column) and did the sort which rearranged the 4th column. Then I just copied the original (unsorted) 4th column over the new wrongly sorted 4th column and tried sorting again. Now keep in mind that I just sorted the data, and I am only sorting by the 1st column, so it should already be in order. Well, when I sorted again, the 4th column still got rearranged. Very strange!

Have you heard of any problems with version 9.7?
I could send you the file with the exact data to see if it works in earlier versions.

 

[DaleWatson123321]

syl93,

Very weird, indeed!

Yes, if you send me the file, I'll be only too pleased to check it out. My curiosity level has risen.

Like I said, I tested the numbers you posted (with no problem), but it's possible there's something different about the actual numbers in your file.

You can reach me at my HOME address today. I'll quickly test the file and get back to you. I'll also post the results, just in case it might be of interest to someone else.

Reminder: to send the file as a "WK4" file.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[captjlddavis]

I find this very interesting and have beeen following along.

I too have tied all of the steps along the way.

Win 98SE 123 version 5.0.0.544 (5.0A)

I noticed that if you run the macro 4 times it will return to the original order. runs 1,2 & 3 always return the same results. I thought it might be in the recalculations, but changed the method from Natural, to column , to rows and
played with the iterations but it still does the same thing.

I will keep and eye on this post and see what comes up.

regards Capt Jim

 

[DaleWatson123321]

syl93,

After looking at your file, the "light bulb" (finally) went on. It should have before, but it's been awhile since I worked with Lotus 123's sorting function.

I'm reminded that when all the fields being referenced by the sort macro are "identical", then the sort function does NOT maintain control over the sort order of the "other" fields included in the sort range but NOT referenced by the sort macro.

I hope this makes sense.

The solution of course is to include the extra column(s) in the sort macro - at least up to the number of columns required to maintain the sort order you require. With this in mind, I've modified your macro, as follows...

/DSRDA:A7.A31~PA:A7~A~SA:B7~A~E1~A:C7~A~E2~A7~A~G

The data range is now from row 7-31, because of inserting the new macro.

I tested the data in Excel, and Excel DOES maintain the proper order, but as discussed Excel can only have 3 sort fields. I MUCH prefer having the additional sorting capability of Lotus 123, with what appears to be an "endless" number of sort fields.

Best Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[syl93]

Dale,

Thank you so much for your explanation. I was pulling my hair out trying to figure out this problem! At least now I know the cause!

 

[captjlddavis]

Dale:
(from capt jim)
It took me awhile to follow your logic(and macro)but now all is clear.

Digressing a bit, I posted a question regards 123r5 and smartsuite on co existing on same machine but haven't seen any reply. Any comments too that post wud be appreciated.

 

[DaleWatson123321]

Capt. Jim, Sir,

I've been terribly busy lately, so probably missed the posting you referred to.

I can confirm that I've had the following versions of Lotus 123 on my PC at the SAME time, and never encountered any problems.

version 3.4
version 5.0
version Smartsuite '97

I therefore would not expect you to have any problems. However, I didn't keep Smartsuite '97 on my PC for very long, as I was NOT pleased AT ALL with the "backwards" direction of the "new" product.

A good practice (for all software) is to keep your data in SEPARATE folders - i.e. NOT as sub-folders under the software. Then, if and when you remove the software, you don't have to worry about removing your data at the same time.

I hope this helps.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca