1. ## Vlookup

HI All,

I have the below information in a sheet.

Name Fruits
Peter Fruits Apple
Peter Fruits Pear
Peter Fruits Banana
Sally Fruits Apple
Sally Fruits Pear

I need it to show this way instead

Name Fruits
Peter Apple, Pear, Banana
Sally Apple, Pear

Anyone can advise how can I do that?
Thanks!

2. ## Transpose unique and returns the corresponding data

Originally Posted by yewee
I have the below information in a sheet.
Try the ARRAY formula for unique names set next formula. The formula copy down
Code:
`=INDEX(\$A\$2:\$A\$6;MATCH(0;COUNTIF(\$A\$2:\$A\$6;"<"&\$A\$2:\$A\$6)-SUM(COUNTIF(\$A\$2:\$A\$6;"="&F\$1:F1));0))`
ARRAY formula that will return the fruits of condition names, try the following. Copy this formula to the right
Code:
`=IF(ISERROR(INDEX(\$C\$2:\$C\$8;SMALL(IF(\$A\$2:\$A\$8=\$F2;ROW(\$B\$2:\$B\$8)-ROW(\$C\$1));COUNTA(\$F2:F2));1));"";INDEX(\$C\$2:\$C\$8;SMALL(IF(\$A\$2:\$A\$8=\$F2;ROW(\$B\$2:\$B\$8)-ROW(\$C\$1));COUNTA(\$F2:F2));1))`

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•