Results 1 to 2 of 2

Thread: Vlookup

  1. #1

    Vlookup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013

    Transpose unique and returns the corresponding data

    Quote Originally Posted by yewee View Post
    I have the below information in a sheet.
    You should have with the issue, set an example for download
    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))
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Posting Permissions

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