Need to assistance unpacking a string

I misunderstood from your original description that the content of the cell would be something like

LAX RDU YYZ e.t.c

but in reality it's

LAXRDUYYZ e.t.c

So I had erroneously changed the script from

repeat with i from 1 to the length of inputStr by 3

to

repeat with i from 1 to the length of inputStr by 4

Also some components like 0E8 are converted by Numbers to 0E+00, i.e. 0 x 10^0. To counteract that formatting the cell as text keeps the original value 0E8.

I was actually wondering if Sheet 2 was needed at all. Maybe you had put it just to count the occurrences of each code, but the script counts them so maybe that part could be avoided if that's the case.

So with all these musings I suggest the following version. See in next message.

I confess I may not understand the aim here, and why a script is the preferred way (maybe hundreds of strings to uppack?), but here is a simple way to unpack the string and find the most commonly recurring values:

Table 2:

A2: input 1

A3, and filled down (drag down enlarge table until no value in B, in the example to row 210):

=$A2+3

B2:

=MID(Table 1::$A$2,$A2,3)

Then apply 'is distinct' filter on column B as in the screenshot.

Create a Table 3.

Select B and C of Table 2, and command-c to copy.

Click once in upper left cell in Table 3, the Edit > Paste Formula Results.

Sort descending on column B.

You can also skip creating a filter on Table 2 and get the sorted results quickly using a Pivot Table, like this:

Click in Table 2, from the menu choose Organize > Create Pivot Table > On Current Sheet.

Drag fields as shown in screenshot, change 'Count (Sum)' to 'Count (Average)' by clicking the circled i.

Sort descending on B.

SG

Since posting long scripts in the discussions is pretty tedious, here's a link to the complete thing

Unpack and count codes 2.0

Try with a shorter string including permutations of code letters.

PART 2

sort(codeList, 1, -1, {comparer:codeCount_ascending})

set leastUsedCodes to items 1 thru 5 of codeList
set mostUsedCodes to reverse of (items -5 thru -1 of codeList)

tell front document of application "Numbers"
	
	tell rankedData
		if row count < 11 then set row count to 11
		
		repeat with i from 1 to count of mostUsedCodes
			set value of cell (i + 1) of column 2 to (code of item i of mostUsedCodes)
			set value of cell (i + 1) of column 3 to (codeCount of item i of mostUsedCodes)
		end repeat
		
		repeat with i from 1 to count of leastUsedCodes
			set value of cell (i + 6) of column 2 to (code of item i of leastUsedCodes)
			set value of cell (i + 6) of column 3 to (codeCount of item i of leastUsedCodes)
		end repeat
	end tell
	
end tell


on addToList(theList, theItem)
	set foundCode to false
	
	repeat with anItem in theList
		if code of anItem is theItem then
			set anItem's codeCount to (anItem's codeCount) + 1
			set foundCode to true
		end if
	end repeat
	
	if not foundCode then
		set end of theList to {code:theItem, codeCount:1}
	end if
end addToList


script codeCount_ascending
	-- Compare two records by codeCount property.
	on isGreater(a, b)
		(a's codeCount > b's codeCount)
	end isGreater
end script

continued...

middle of PART 1, version 2

-- keep original part here
	
	repeat with aCell in sourceTable's column 1's cells
		
		set inputStr to value of aCell
		
		if inputStr is not missing value then
			
			-- Unpack the string into 3 digits until exhausted
			
			set digits to {}
			repeat with i from 1 to length of inputStr by 3
				try -- in case the number of characters is not a multiple of 3
					-- if using sheet 2
					--set end of digits to text i thru (i + 2) of inputStr
					
					-- if not using sheet 2
					set aCode to text i thru (i + 2) of inputStr
					my addToList(codeList, aCode)
				end try
			end repeat
			
			(*
			-- not needed if going directly from Sheet 1 to Sheet 3
			-- Write the digits to the new sheet
			tell destTable
				set newRow to add row below last row
				set countDigits to count of digits
				if column count < countDigits then set column count to countDigits
				repeat with i from 1 to countDigits
					set format of newRow's cell i to "text" -- new line to take care of codes like 0E8
					set value of newRow's cell i to item i of digits
					my addToList(codeList, value of newRow's cell i)
				end repeat
			end tell
			*)
		end if
		
	end repeat
	
-- keeep original part here

I can assure you that my list is the correct one based on the 627-character string you posted. I tested it four different ways, with Numbers Find, with the script, with COUNTMATCHES on the full string and with COUNTIF on the separated parts (on Sheet 2, reactivated part of the script). Note: COUNTMATCHES gives a higher result for AAA because there are a lot of them grouped together).

It looks like you haven't put or called the CustomQuicksort part yet. That's how it would look with it.

0E8 has 12 instances, hence it doesn't make the top-5.

Little improvement for speed to the addToList handler.

on addToList(theList, theItem)
	-- version 3
	repeat with anItem in theList
		if code of anItem is theItem then
			set anItem's codeCount to (anItem's codeCount) + 1
			return
		end if
	end repeat
	
	set end of theList to {code:theItem, codeCount:1}
end addToList

Finally, don't worry, ORD and DOR will be counted separately, they are different strings.

Forgot to mention formula in B2 of Table 2, filled down, is:

=COUNTIF(B,B2)

SG

Start with that.

PART 1

-- Get the active sheet
tell front document of application "Numbers"
	set sourceTable to table "Table 1" of sheet "Sheet 1" -- if you define an object use it, see in code
	set destTable to table "Table 1" of sheet "Sheet 2"
	set rankedData to table "Table 1" of sheet "Sheet 3"
	set codeList to {}
	
	set destTable's row count to 1
	
	repeat with aCell in sourceTable's column 1's cells
		
		set inputStr to value of aCell
		
		if inputStr is not missing value then
			
			-- Unpack the string into 3 digits until exhausted
			set digits to {}
			repeat with i from 1 to length of inputStr by 4
				set end of digits to text i thru (i + 2) of inputStr
			end repeat
			
			-- Write the digits to the new sheet
			tell destTable
				set newRow to add row below last row
				set countDigits to count of digits
				if column count < countDigits then set column count to countDigits
				repeat with i from 1 to countDigits
					set value of newRow's cell i to item i of digits
					my addToList(codeList, value of newRow's cell i)
				end repeat
			end tell
			
		end if
		
	end repeat
	
end tell

continued...

PART 3

-- https://mac.softpedia.com/get/Utilities/A-Dose-of-Sorts.shtml
(* Quicksort — customisable version
Algorithm: S.A.R. (Tony) Hoare, 1960.
AppleScript implementation: Arthur J. Knapp and Nigel Garvey, 2003.
Very minor modifications by Nigel Garvey 2007 & 2010.
NG's version of the customisation, rethought 2010.

Parameters:
New: (list, range index 1, range index 2, record with optional 'comparer' and 'slave' properties). The 'comparer' value is a script object containing an isGreater(a, b) handler which determines if object a is "greater" than object b. The 'slave' value is a script object containing a swap(a, b) handler, which is called when items a and b of the list being sorted are swapped, and a shift(a, b) handler, which is called when the insertion sort moves item b of the list being sorted to position a, the intervening items being shifted up one position in the process.
Or, for compatibility with the customisation for the older version of CustomQsort: (list, range index 1, range index 2, script object containing the isLess(a, b), swap(a, b), and shift(a, b) handlers mentioned above)
Where the 'comparer' or 'slave' properties are omitted, or the customisation parameter isn't a record or a script object, the sort has default handlers which respectively compare items directly and do nothing.
*)

on CustomQuicksort(theList, l, r, customiser)
	script o
		property comparer : me
		property slave : me
		
		property lst : theList
		
		on qsrt(l, r)
			-- Get an item from the middle of the range to use as a pivot value.
			set pivot to my lst's item ((l + r) div 2)
			
			-- Traverse the range from both ends, swapping items from the low end whose values are greater than or equal to the pivot with items from the high end whose values are less than or equal to it. Stop when the traversals cross.
			set i to l
			set j to r
			repeat until (i > j)
				-- Search from the low end for the next greater-or-equal value.
				set u to my lst's item i
				repeat while (comparer's isGreater(pivot, u))
					set i to i + 1
					set u to my lst's item i
				end repeat
				

continued...

PART 2, version 2

sort(codeList, 1, -1, {comparer:codeCount_ascending})

set leastUsedCodes to items 1 thru 5 of codeList
set mostUsedCodes to reverse of (items -5 thru -1 of codeList)

tell front document of application "Numbers"
	
	tell rankedData
		if row count < 11 then set row count to 11
		set format of columns 2 thru 3 to "text" -- new line to take care of codes like 0E8
		
		repeat with i from 1 to count of mostUsedCodes
			set value of cell (i + 1) of column 2 to (code of item i of mostUsedCodes)
			set value of cell (i + 1) of column 3 to (codeCount of item i of mostUsedCodes)
		end repeat
		
		repeat with i from 1 to count of leastUsedCodes
			set value of cell (i + 6) of column 2 to (code of item i of leastUsedCodes)
			set value of cell (i + 6) of column 3 to (codeCount of item i of leastUsedCodes)
		end repeat
	end tell
	
end tell

-- keep original part here

Version 2 updates complete

PART 4

				-- Search from the high end for the next lesser-or-equal value.
				set w to my lst's item j
				repeat while (comparer's isGreater(w, pivot))
					set j to j - 1
					set w to my lst's item j
				end repeat
				
				if (i > j) then
				else
					-- Swap the two values (even if they're the same!) and continue.
					set my lst's item i to w
					set my lst's item j to u
					
					-- Tell the slave object this exchange has occurred.
					slave's swap(i, j)
					
					set i to i + 1
					set j to j - 1
				end if
			end repeat
			
			-- At this point, items l thru j are all greater than or equal to the pivot and items i thru r are all greater than or equal to it. There may be an item BETWEEN j and i, which will be an instance of the pivot in exactly the right place. This can be ignored.
			
			-- Recursively apply the process to the lesser-or-equal items and then to the greater-or-equal items. When both recursions have returned, the range will be fully sorted.
			if (j > l) then qsrt(l, j)
			if (i < r) then qsrt(i, r)
		end qsrt
		
		-- Default comparison and slave handlers for an ordinary, qSort-style sort.
		on isGreater(a, b)
			(a > b)
		end isGreater
		
		on swap(a, b)
		end swap
	end script
	

continued...

PART 5

	-- Process the input parmeters.
	set listLen to (count theList)
	if (listLen > 1) then
		-- Negative and/or transposed range indices.
		if (l < 0) then set l to listLen + l + 1
		if (r < 0) then set r to listLen + r + 1
		if (l > r) then set {l, r} to {r, l}
		
		-- The customisation parameter for the earlier version of CustomQsort was a script object containing both comparison and slave handlers. Accept that, if presented; otherwise expect a record containing comparer and/or slave properties, whose values are script objects containing the comparison and slave handlers respectively. If either property is omitted from the record, or if the parameter isn't a script object or a record, use the appropriate default handers in the script object above.
		if (customiser's class is script) then
			try -- Use the customising script's isLess handler, if it has one.
				customiser's isLess
				set o's comparer to customiser
			end try
			try -- Use the customising script's slave handlers, if it has them.
				customiser's swap
				customiser's shift
				set o's slave to customiser
			end try
		else if (customiser's class is record) then
			set {comparer:o's comparer, slave:o's slave} to customiser & {comparer:o, slave:o}
		end if
		
		-- Do the sort.
		o's qsrt(l, r)
	end if
	
	return -- nothing.
end CustomQuicksort

property sort : CustomQuicksort

** COMPLETE **