How to do this awk one-liner in Calc?

naoki · Newbie Joined: 01 May 2008 Posts: 2

cat list | awk '{array[$1]+=$2} END {for (i in array) print i,"= ",array[i] }'

I've only just started using calc in earnest and have been reading up on countif and the like. But it's just not quite what I want because it doesn't build the array for me, unlike this handsome awk one-liner you see above.

The data:

$ cat list
data1 10
data2 10
data3 20
data1 15
data2 9
data5 18
data4 5
data1 1

And the output:
$ cat list | awk '{array[$1]+=$2} END {for (i in array) print i,"= ",array[i] }'
data1 = 26
data2 = 19
data3 = 20
data4 = 5
data5 = 18

(I know, cool isn't it).

J_Eduardo · General User Joined: 10 Apr 2008 Posts: 10

Use SUMIF instead of COUNTIF.

JohnV · Posted: Fri May 02, 2008 5:22 am Post subject:

Assuming data1, etc., is in A2:A9 and 10, etc., is in B2:B9 then in C2 put this formula and copy it down to C6:
="data" & ROW()-1 & " = " & SUMIF(A2:A9;"=data" & ROW()-1;B2:B9)

Villeroy · Posted: Sat May 03, 2008 7:35 am Post subject:

Data>Subtotals...
or Data>Data Pilot>Start... (names to columns, numbers to data)
or filter unique names to another column (Data>Filter... copy output, no dupes) and apply SUMIF
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

naoki · Newbie Joined: 01 May 2008 Posts: 2

The names "data1, data2..." could be anything, in my example it's data1/2/3/4.. but the data set I am actually working on comprises of a range of non-linear text.

Found this article roughly the same question is addressed in the comments :
http://www.openofficetips.com/blog/archives/2004/12/basic_functions_7.html

But sadly I am yet to find an answer simple enough for a beginner like myself.

Will keep at it though.

Villeroy · Posted: Wed May 07, 2008 3:45 am Post subject:

If you have programming experience you have to learn one new concept: Absolute and relative addressing. This is basically the same fundamental concept of all spreadsheets. My quick tutorial, but any old spreadsheet book should explain this better.

Every cell can take one of number (double), string or formula. All dates, times and booleans are nothing but formatted numbers (double). Formulas return numbers, strings or errors while taking numbers, strings or arrays of strings and numbers as arguments. In boolean context any number <>0 evaluates to True. There exist special array-formulas (special syntax), which may return more than one value.
That's almost all you should to know about the cells.
Most of the other tools control the output by means of printing, charting, formatting, rearranging (Menu:Data). Some tools import values.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org