OpenRefine, how to merge row X values based on row Y unique value

Question

In OpenRefine, how can I go from this:

ID    criteria  
=====.============
40006 John Doe|1985-05-15
50008 John Doe|1985-05-15  
23012 John Doe|1985-05-15
90001 Ferry Hart|1978-09-23
 9031 Ferry Hart|1978-09-23

to this:

ID's                criteria
===================.=================== 
40006, 50008, 23012 John Doe|1985-05-15
90001, 9031         Ferry Hart|1978-09-23

This looks like the right answer. Blanking down does produce this:

ID    criteria  
=====.============
40006 John Doe|1985-05-15
50008 
23012
90001 Ferry Hart|1978-09-23
 9031

But Join multi-valued cells on ID doesn't seem to work. This might be because all my rows are also records.

Any suggestions?

Answer 1

The crux of the matter is that you can turn sets of distinct rows - that is, rows with their own unique serial number in the All column - into records by blanking if and only if the column you're blanking down is at the beginning.

(Let's say 'a record' is a row with a serial number plus some rows below it with no serial number, that belong to the record. )

What follows below is derived from this, thanks to magdmartin. Here are the steps.

1. Make sure the criteria column is trimmed and that you're in row mode

2. criteria -> edit column -> move column to the beginning.

3. criteria -> edit cells -> blank down. This turns sets of rows into records.

4. switch to record mode. You'll now see records: only rows with a criteria have a serial number.

5. ID -> add column based on this column, enter this GREL:

   forEach(row.record.cells['ID'].value,v,v).join('|')

   That creates the desired field contents. You can blank that down too.

6. criteria -> Facet -> Customized Facet -> Facet by blank (null or empty string)

7. All -> Facet -> Facet by blanks to select only empty rows

8. All -> Edit rows -> remove matching rows

9. In the Facet box on the left, select the remaining rows.

Yay!