Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to combine data from different rows?

I'm relatively new to Numbers and think there should be an easy solution for this problem!


I have a huuuuuuuge spreadsheet that looks like this:

User uploaded file

And I want it to be this way:

User uploaded file


So, basically combining data from rows based on the "Title" value being empty. I understand it may be necessary to copy the "Title" value to the empty rows first. I actually did find a solution to do this in MS Excel, but only the Windows version :/


Please help me out! 🙂

Posted on Apr 25, 2015 1:38 PM

Reply
Question marked as Best reply

Posted on Apr 26, 2015 6:30 AM

Hi Blandine,


Here is one approach. First I did fill the blank "Title" cells. This is how I did that. I put "=A2" in A3 and copied. I used a filter to hide all nonblank column A cells and pasted it into the A column. Re titled the column.

User uploaded file

The formula in D1=IF($A2=$A1,D1&", "&B2,B2)

This is filled down and to the right.

Before you delete the original B and C columns you need to copy columns D and E and Paste Formula Results back into them.


That should do it.


quinn

7 replies
Question marked as Best reply

Apr 26, 2015 6:30 AM in response to Blandine

Hi Blandine,


Here is one approach. First I did fill the blank "Title" cells. This is how I did that. I put "=A2" in A3 and copied. I used a filter to hide all nonblank column A cells and pasted it into the A column. Re titled the column.

User uploaded file

The formula in D1=IF($A2=$A1,D1&", "&B2,B2)

This is filled down and to the right.

Before you delete the original B and C columns you need to copy columns D and E and Paste Formula Results back into them.


That should do it.


quinn

Apr 26, 2015 6:38 AM in response to t quinn

Hey Quinn,


First of all thank you so much for your help!


What am I doing wrong? I'm getting a syntax error (by the way, SE is IF in Portuguese 🙂)

User uploaded file


User uploaded file


Also as a secondary question, I managed to filter the blank rows out and use =A2 in A3 to copy values, but how do I auto-extend that formula to all the cells? I thought there would be a click-drag on the bottom right of the cell, but as soon as I hit enter with =A2 the cell goes "okay, not empty anymore, getting out of this filter". Haha


Again, thank you so much for your help 😀

Apr 26, 2015 9:30 AM in response to Blandine

Hi Blandine,


I suspect your version of Numbers wants different separators between the arguments of IF(). Somethng I do when I am having syntax issues is to recreate the formula by clicking only and letting Numbers assemble the formula. If it wants a semicolon between arguments taht is what it will use.


#2- After I copied the formula and used the filter I clicked the column header and pasted. I then had to delete the formula in the header. When it comes time to delete your unwanted rows you will need to copy and paste formula results in this column also.


quinn

May 1, 2015 1:46 PM in response to Blandine

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:


User uploaded file




Tested with Numbers v2 under OS X 10.6.8.


Good luck,

H

How to combine data from different rows?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.