Excel: Group values from rows based on common cell

问题:

I have an Excel file that contains a list of groups and the users that belong to them. I need to generate a list of users that belong to each group. Key point: while this is a trivial task in perl, python, or any other programming language, I'd like to do the whole thing in Excel so I don't have to export to csv, re-import the results, and re-do the formatting every time there's a change.

The file format (when exported to csv) is akin to:

Group, username, email
Perl Jam, evedder, [e-mail]
Perl Jam, mcameron, [e-mail]
Perl Jam, jament, [e-mail]
Perl Jam, sgossard, [e-mail]
Perl Jam, mmccready, [e-mail]
Soundgarden, mcameron, [e-mail]
Soundgarden, ccornell, [e-mail]
Soundgarden, kthayill, [e-mail]
Soundgarden, bshepherd, [e-mail]
Temple of the Dog, evedder, [e-mail]
Temple of the Dog, ccornell, [e-mail]
Temple of the Dog, jament, [e-mail]
Temple of the Dog, sgossard, [e-mail]
Temple of the Dog, mmccready, [e-mail]
Temple of the Dog, mcameron, [e-mail]

What I'd like to end up with is:

Group, usernames
Perl Jam, evedder, mcameron, jament, sgossard, mmccready
Soundgarden, mcameron, ccornell, kthayill, bshepherd
Temple of the Dog, evedder, mcameron, ccornell, jament, sgossard, mmccready

回答1:

To get your unique list use this array formula:

=IFERROR(INDEX($A$2:$A$16,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$16),0)),"")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Put in first cell, hit Ctrl-Shift-Enter, then copy down till you get blanks.

To get the username:

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$16)/($A$2:$A$16=$E2),COLUMN(A:A))),"")

Where $E2 is the cell in which the other formula is.

Then copy across and down.