Transposing Columns

Scripting Forums AppleScript | Mac OS X
#1

I am trying to manipulate a table of data originally I wanted to work out she Excel but at present I cannot get it to work in Excel. The test in the following script is to reverse the order of the columns Last one first Last but one second and so on. The table I thought I was building is called Revised list and it is supposed to be pasted to a new worksheet in reverse order. From a 9 by 11 matrix it enters one row of N/A

tell application "Microsoft Excel"
	set OriginalList to value of used range of active sheet
	set RowCount to count of OriginalList
	set ColCount to count item 1 of OriginalList
	set FirstCell to (get address of cell 1 of column (ColCount))
	set LastCell to get address of cell RowCount of column (ColCount)
end tell
set RevisedList to {}
repeat with x from ColCount to 1 by -1
	tell application "Microsoft Excel"
		set FirstCell2 to (get address of cell 1 of column x)
		set LastCell2 to (get address of cell RowCount of column x)
		set CurrCol to value of range (FirstCell2 & ":" & LastCell2)
		set end of RevisedList to CurrCol
		log CurrCol
	end tell
end repeat
log RevisedList
tell application "Microsoft Excel"
	make new worksheet at after last sheet of active workbook --Sheet to transfer data to
	set value of range ("A1:" & LastCell) to RevisedList
end tell
#2

Three ways to do it that I can think of off hand…

  1. If using Excel 365:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

tell application "Microsoft Excel"
	tell active workbook
		tell active sheet
			tell used range
				set columnCount to count of columns
				set usedRngAddr to get address
			end tell
			--	=CHOOSECOLS(A1:E27,SEQUENCE(1,5,5,-1))
			-- build a reverse sequence of column indices
			set seq to "SEQUENCE(1," & columnCount & "," & columnCount & ",-1)"
			-- assemble the columns in reverse order
			set fml to "=CHOOSECOLS(" & usedRngAddr & "," & seq & ")"
			set newRng to evaluate name fml
		end tell
		
		set newSh to make new worksheet at after last sheet
		tell newSh to set value of range usedRngAddr to newRng
	end tell
end tell
  1. If using earlier versions of Excel that don’t have the SEQUENCE and CHOOSECOLS functions:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

tell application "Microsoft Excel"
	tell active workbook
		tell active sheet
			tell used range
				set columnCount to count of columns
				set usedRngAddr to get address
				
				set newColOrder to {}
				repeat with idx from columnCount to 1 by -1
					set end of newColOrder to value of column idx
				end repeat
			end tell
		end tell
		
		set newSh to make new worksheet at after last sheet
		tell newSh
			tell range usedRngAddr
				repeat with idx from 1 to columnCount
					set value of column idx to item idx of newColOrder
				end repeat
			end tell
		end tell
	end tell
end tell
  1. Using AppleScriptObjC:
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
use framework "Foundation"

property NSArray : a reference to current application's NSArray

tell application "Microsoft Excel"
	tell active workbook
		tell active sheet
			tell used range
				set usedRngAddr to get address
				set usedRngRows to value
			end tell
		end tell
	end tell
end tell

set newRngRows to {}
repeat with eachRow in usedRngRows
	set end of newRngRows to (((NSArray's arrayWithArray:eachRow)'s reverseObjectEnumerator())'s allObjects()) as list
end repeat

tell application "Microsoft Excel"
	tell active workbook
		set newSh to make new worksheet at after last sheet
		tell newSh
			tell range usedRngAddr
				set value to newRngRows
			end tell
		end tell
	end tell
end tell
#3

Thank you so much I have been struggling for days and resorted to ChatGPT which got me into a worse mess. Forgive me for being lazy but my initial issue was the start of a wider problem. My table download from the web needs a number of columns to be changed I had solved the issue in the past moving one column at a time but that is very cumbersome, do I need to use a series of sequence steps to move multiple columns? thanks again

#4

I mean, it’s kind of hard to say without more details on your before state and what you want the end state to be. Also, without knowing which version of Excel you are using.

But if you are using Excel 365, you can construct a CHOOSECOLS formula using an array constant to reorganize the columns in whatever order you want.

So this from my example above:

-- =CHOOSECOLS(A1:E27,SEQUENCE(1,5,5,-1))
-- build a reverse sequence of column indices
set seq to "SEQUENCE(1," & columnCount & "," & columnCount & ",-1)"
-- assemble the columns in reverse order
set fml to "=CHOOSECOLS(" & usedRngAddr & "," & seq & ")"
set newRng to evaluate name fml

reverses the order of the columns by building a reversed sequence of the column indices. The formula SEQUENCE(1,5,5,-1) just evaluates to an array {5,4,3,2,1}. So you can use the fact that CHOOSECOLS can take an array constant to put the columns in any order you want, like so:

-- =CHOOSECOLS(A1:E27,{1,4,5,3,2})
-- assemble the columns our desired order
set fml to "=CHOOSECOLS(" & usedRngAddr & ",{1,4,5,3,2})"
set newRng to evaluate name fml

which reorders them 1, 4, 5, 3, 2.

If you aren’t using Excel 365, things get trickier. Before I dive into that, though, what version of Excel are you using?

#5

Thank you again, managed to get it without using sequence, it’s amazing discovering what one does not know. Just getting old I guess VisiCalc to Lotus 123 to Excel still don’t understand a lot of what it offers. As said originally I was really trying to work with a table outside Excel. But your solution works which is what matters, thanks again.