Create list from duplicate rows

Is that what you want? (with PowerQuery) (if 2017 is real version of Excel - it means this is a Mac but Office for Mac doesn't support PQ)

If you have Excel 2016 (or better?) then there should be better options than the one I came up with (like sandy's, maybe? I'm stuck with Excel 2010), but the following should work with older versions. To return the animal in A34, the formula below should be array entered (confirm with Ctrl+Shift+Enter instead of Enter):

=IFERROR(INDEX($A$2:$A$26,SMALL(IF(COUNTIF($A$33:$A33,$A$2:$A$26)=0,ROW($A$2:$A$26)-ROW($A$2)+1),1)),"")

Fill down for the other animals. In B34 to get the colors:

=SUBSTITUTE(TRIM(IF(SUMIF($A$2:$A$26,$A34,$B$2:$B$26)>0,$B$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$C$2:$C$26)>0,$C$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$D$2:$D$26)>0,$D$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$E$2:$E$26)>0,$E$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$F$2:$F$26)>0,$F$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$G$2:$G$26)>0,$G$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$H$2:$H$26)>0,$H$1,"")&" "&IF(SUMIF($A$2:$A$26,$A34,$I$2:$I$26)>0,$I$1,""))," ",",")

Fill down for the others. This is not a very sophisticated approach, since it requires an IF clause for each color, but it does appear to work.

I overlooked this

Originally Posted by ian2

A comma delimited list of colors for each animal would be ideal

. sorry

but...
(as I said before - PQ doesn't work with Mac)