Hello
If it is one time conversion and text in cell does not contain tab or newline character, you may try the following shell script via Automator's Run Shell Script action.
To use it, copy the source range in Numbers to the clipboard , run the workflow and it will put the processed TSV data in the clipboard which you can paste into a new table in Numbers.
Automator workflow consists of single Run Shell script action such that
- Shell = /bin/bash
- Pass input = as arguments
- Code = as follows
#!/bin/bash
export LC_ALL=en_GB.UTF-8 # UTF-8 locale is required for pbcopy and pbpaste to handle UTF-8 text correctly
ruby -w <<'EOF' - <(pbpaste) | pbcopy
#
# v0.21 -
#
# * internal hash table is created such that
# { a[i][k] => { j => [ a[i*][j]; for i* : a[i*][k] = a[i][k] ]; for j }; for i }
# where
# k = consolidation key index
# i = row index
# j = column index
# a[x][y] denotes data at coorditates [x][y] of array a
#
def array2text(aa, opts = {})
# array aa : 2-d array
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
fs, rs = {:fs => %[\t], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
return aa.map {|a| a.join(fs) }.join(rs)
end
def text2array(t, opts = {})
# string t : text representation of 2d-array
# hash opts : {:fs => fs, :rs => rs}
# string fs : field separator
# string rs : record separator
fs, rs = {:fs => %[\t], :rs => %[\n]}.merge(opts).values_at(:fs, :rs)
return t.split(rs).map {|a| a.split(fs)}
end
key_index = 0 # consolidation key index (0-based column index)
hh = {} # internal hash table
k1 = '-' # k1 = last key
kk = [] # keys in order of appearence
jx = 0 # max column index
aa = text2array(ARGF.read)
aa.each do |a|
k = a[key_index] # k = current key
k == '' ? k = k1 : k1 = k # use last key as current key if current key is ''
kk << k unless kk.include?(k)
h = hh.include?(k) ? hh[k] : {}
hh[k] = a.each_with_index.inject(h) do |h, u|
e, j = u
jx = j if jx < j
next h if e == '' # ignore value being empty string
if h.include?(j)
h[j] << e unless h[j].include?(e) # collect only distinct values
else
h[j] = [] << e
end
h
end
end
bb = kk.inject([]) do |bb, k|
bb << (0..jx).inject([]) do |b, j|
b << ((e = hh[k][j]) ? e.join(', ') : '')
end
end
print array2text(bb)
EOF
The Automator workflow will look something like this:
Tested with Numbers v2 under OS X 10.6.8.
Good luck,
H