Q: Appleworks 6.2.9 Spreadsheet Sorting

Hi John,

I checked the alphabetical list of functions in AppleWorks Help, but didn't find COUNTIF, the one I'd use to count the occurrences of each data item.

The other catch 22 in your question is that any formula that counts the occurrences would return the current count. That count would change as items were deleted, as the deleted items would no longer be there to be counted.

You might be able to make some progress tansfering the file to Numbers, or to one of the OpenOffice.org based applications (OpenOffice.org, LibreOffice or NeoOffice), but it will be an at least partially manual task.

Regards,

Barry

Hello

Given source data pre-sorted in column A (e.g., A1..A20), you may use following formulae for columns B, C, D.

B is for accumulated occurrence of data in A

C is for unique entries from A

D is for occurrence of data in C

# A1..A20 : sorted list of data

# B1..B20 : accumulated occurrence of each entry in A
B1 =1
B2 =IF(A2=A1,B1+1,1)
..
B20 =IF(A20=A19,B19+1,1)

# C1..C20 : unique entries from A
C1 =IF(B1<B2,"",A1)
..
C20 =IF(B20<B21,"",A20)

# D1..D20 : occurrence of each entry in C
D1 =IF(B1<B2,"",B1)
..
D20 =IF(B20<B21,"",B20)

* B2..B20, C1..C20, D1..D20 are entered by fill down. Note that B1 = 1.

If you want to eliminate the black data in column C and D, copy C and D and paste value of them to, e.g., columns E and F, and sort E and F by E.

Hope this helps,

H

Hello

You seem to have figured it out by yourself. It's good.

As for the limit of row count in AW6 SS document, it is hard-coded as 16384 which is the same in Intel Mac.

cf.

http://web.archive.org/web/20110725085441/http://docs.info.apple.com/article.htm l?artnum=60732

It states that "Maximum Number of Cells : 256 Columns by 16,384 Rows = 4,194,304 cells".

As for handling large set of data, you may try Calc (Spreadsheet programme) of LibreOffice or Microsoft Excel 2008 or earlier for PPC. (Microsoft Office 2011 is for 10.5.8 or later and Intel only.)

cf.

http://www.libreoffice.org/download/system-requirements

http://support.microsoft.com/kb/980967

http://support.microsoft.com/kb/2581812

By the way, Numbers 09 (part of iWork 09 from Apple, Inc.) will be useless because it is unacceptably slow for handling large data. Indeed I'd say it can only handle very small data. I don't know why it can be so slow, but it is slow.

Good luck,

H

Hello

In case, here's another possibility using Perl, which is  wrapped in AppleScript for convenience.

Recipe.

1) Make the input text file in UTF-8 by copying the column in AW6 SS document and pasting it into plain text document in TextEdit.app.

You may copy and append all data into one plain text document. Save it with name "in.txt" on desktop. Please make sure you set the text encoding for saving file to UTF-8 in TextEdit's preference.

2) Copy the AppleScript code listed below into new document of Script Editor.app and run it.

It will create a plain text file in UTF-8 named "out.txt" on desktop which contains lines with two fields separated by tab, i.e., each line = data entry, tab and its frequency; sorted by frequency in descending order (and sorted by entry in ascending order for those with the same frequency).

--applescript
set infile to (path to desktop)'s POSIX path & "in.txt"
set outfile to (path to desktop)'s POSIX path & "out.txt"

set sh to "
in=" & infile's quoted form & "
out=" & outfile's quoted form & "

perl -CSD -l015 -015 <<'EOF' - \"$in\" > \"$out\" 
my %hash = ();
while (<>) {
    chomp;
    $hash{$_} += 1;
}
local $, = qq(\\t);
for (sort { $hash{$b} <=> $hash{$a} || $a cmp $b } keys %hash) {
    print $_, $hash{$_};
}
EOF
"
do shell script sh
--end of applescript

Notes.

• Input text file is assumed to use CR (U+000D: CARRIAGE RETURN) as line terminator. If you copy the data from AW6 and paste it to TextEdit, this will be the case.

• Output text file uses CR as line terminator.

• If you want the result sorted by frequency in ascending order, change the following part:

for (sort { $hash{$b} <=> $hash{$a} || $a cmp $b } keys %hash) {

to:

for (sort { $hash{$a} <=> $hash{$b} || $a cmp $b } keys %hash) {

Kind regards,

H

Hi JL,

IIRC, Fill down in MS Excel works in a manner similar to Numbers—you can do it by draagin a Fill control handle (solid black square handle at the bottom rigt corner of a selected cell) down the column, or you can use Copy / Paste.

The Copy / Paste technique is to:

1. Enter the formula in the top cell of the range. Press Enter to confirm the entry.
   
   
2. If that moves your selection off the cell, click o the cell again to select it.
   
   
3. Copy.
   
   
4. Scroll to the bottom of the range to receive the formula.
   
   
5. Press and hold Shift, and click on the last cell in the range to select the full range.
   
   
6. Paste.

Regards,

Barry