Results 1 to 5 of 5

Thread: Print returned array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Print returned array

    I am looking for a way to print a returned array for a specific criteria.

    For example, if the following values in a column do not equal 0, I would like to return them and print them in another column. I do not want to use a Macro.

    i.e.
    0
    0
    0
    0
    Apples
    0
    0
    Oranges
    0
    0


    Values: "apples","oranges"


    Is this an index function? Thank you for any help.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Print returned array

    Try:

    ABC
    1ListList2
    20Apples
    30Oranges
    40
    50
    6Apples
    70
    80
    9Oranges
    100
    110
    12

    Sheet6



    Worksheet Formulas
    CellFormula
    C2=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/($A$2:$A$20<>0),ROWS($C$2:$C2))),"")

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print returned array

    Thank you! Is there a way I can tighten this up so that
    i.e.
    0
    0
    0
    0
    Apples
    Apples
    0
    0
    Oranges
    0
    0

    Only returns
    Apples
    Oranges

    And not apples twice?

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print returned array

    Never mind this, I can just filter it.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Print returned array

    Filtering works, you can also change the formula to:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/(($A$2:$A$20<>0)*(COUNTIF($C$1:$C1,$A$2:$A$20)=0)),1)),"")
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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