# In a Google spreadsheet, how do I list unique items, modified by a regexp?

I have a Google spreadsheet with items and prices. Column `D` has the prices, column `C` has item names, some of which contain multiple words. When an item has multiple words, only the first one is really significant.

What I'd like to achieve is this:

1. Process the item list to strip off anything after the non-alpha character,
2. SORT(UNIQUE()) the results,
3. SUM() all the items that were matched to create this new list.

I will then generate a pie chart (this is food, after all) with the results. So for example, I might have:

``````  1    C             | D
-------------------+------------
2    Cheese        | \$9.08
3    Milk, 2L      | \$3.89
6    Apples Fuji   | \$2.75
8    Milk 4L       | \$4.75
9    Apples        | \$2.25
``````

The end result will be:

``````  Cheese   | \$9.08
Milk     | \$8.64
Apples   | \$5.00
``````

I know that I can use `REGEXEXTRACT()` to parse an individual field:

``````=REGEXEXTRACT(C3; "^([A-Za-z]+)")
``````

returns `Milk`, as I would expect. As well, I know that I can add up totals for a column using `FILTER()`:

``````  L                   | M
--------------------+----------
=UNIQUE( C2:C )     | =SUM( FILTER( D\$2:D; L2=C\$2:C ) )
=CONTINUE(C2, 2, 1) | =SUM( FILTER( D\$2:D; L3=C\$2:C ) )
...
``````

But I want to combine these, and add up the totals of the items as modified by `REGEXEXTRACT()`. I was hoping I'd be able to do something like:

``````=UNIQUE( REGEXEXTRACT( C2:C; "^([A-Za-z]+)" ) )
``````

and

``````=SUM( FILTER( D\$2:D; L2=REGEXEXTRACT(C\$2:C, "^([A-Za-z]+)") ) )
``````

But when presented with a range, the `REGEXEXTRACT()` function delivers just a string, not an array. This is confirmed, because when I try the formula:

``````=REGEXEXTRACT(UNIQUE(C2:C); "^([A-Za-z]+)")
``````

the error I get is "Not a string: Cheese".

So my question is ... How do I make this work?

Do I need to make a "scratch" worksheet as a bridge? Is it possible to `REGEXEXTRACT()` each element of a `UNIQUE()` result set within a formula?

• Your list is a bit in-homogeneous. Milk is listed as `Milk, 2L` and `Milk 4L`. If you normalize in the first place, then the data is much more consistent; `Item Description` + `, ` + `Remark`. This way you can use the `SPLIT` function, in combination with the `INDEX` function to extract the first result. Come to think of it, the package unit (1L, 2L, 4L, 3/5\$) can/must be noted separately, for reasons of inventory listing, cost price calculation. – Jacob Jan Tuinstra Jul 11 '13 at 5:46
• Where you able to apply the solution @AdamL provided? If yes, then please mark the answer as best answered. – Jacob Jan Tuinstra Oct 9 '13 at 6:06

My approach would be to "normalise" the data first:

`=ArrayFormula(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B))`

and then you can actually wrap this directly in a QUERY function to produce the desired output:

`=ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 label sum(Col2) ''";0))`

You can even output a header row as well:

`=ArrayFormula(QUERY(IF({1,0};IFERROR(REGEXEXTRACT(A2:A;"^([a-zA-Z]+)"));B2:B);"select Col1, sum(Col2) where Col1 != '' group by Col1 label Col1 'Food Item', sum(Col2) 'Total'";0))`

• Can you explain what `IF({1,0}` does? – anon Jan 27 '14 at 5:16