+ Reply to Thread
Results 1 to 5 of 5

same result without array formula?

  1. #1
    Registered User
    Join Date
    06-15-2016
    Location
    BC
    MS-Off Ver
    excel 2013
    Posts
    52

    same result without array formula?

    Hello,

    I've attached a sample doc to work from.
    I'm using an array formula to index a list, and pull the unique values that match the criteria.

    The main thing I am hoping to solve is: getting the same result as I have in the sample doc, without using an array formula - is that possible? I want to pull in the 'feature' listed next to each person and populate in a list on a separate sheet.

    Second to that, it seems for some reason my formula as is, is taking the 'feature' assigned to a person that is one row up. If you look at 'person 3' in sheet1, the first feature is 'feature1TEST'. However that is in line and should be designated under Person 2, not person 3 and I am not sure why.

    Thank you,
    kaunck
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: same result without array formula?

    1. Change the ARRAY formula to this...
    B4=IFERROR(INDEX(features!$A:$A, SMALL(IF($B$3=features!$B$1:$B100, ROW(features!$B$1:$B100)), ROW(1:1))),"")

    2. Why do you want to do away with the array (woo Im a poet lol)

    3. You could use a regular formula is you add a helper column to the data...
    C2=B2&" "&COUNTIF($B$2:B2,B2)
    copied down

    Then use this to pull your data...
    =INDEX(features!$A$2:$A$45,MATCH(Sheet1!B$3&" "&ROWS($A$1:A1),features!$C$2:$C$45,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-15-2016
    Location
    BC
    MS-Off Ver
    excel 2013
    Posts
    52

    Re: same result without array formula?

    I'll give that a go, thank you.
    Reason to do without the array is it causes a lot of latency, especially when it's needed over 4000 lines (and growing)... makes for editing the sheets quite sluggish.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: same result without array formula?

    OK yes, an array over that much data will tend to slow you down

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: same result without array formula?

    Based on your comment and rep, this worked for you. happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array formula wierdness. Unexpected result
    By Russk68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2016, 02:51 AM
  2. Store result from formula array into variable
    By VitoBdG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2016, 06:42 AM
  3. Replies: 4
    Last Post: 01-09-2016, 09:42 AM
  4. [SOLVED] weekday() result used in an array formula doesn't work. Formula works if i type in date
    By aarco50 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2014, 03:25 PM
  5. How to find out result by array formula
    By alee001 in forum Excel General
    Replies: 8
    Last Post: 04-28-2012, 12:34 AM
  6. Display part of an array formula result
    By brookhyserj in forum Excel General
    Replies: 1
    Last Post: 02-07-2011, 12:57 AM
  7. Performing calculations on the result of an array formula
    By AdamR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2009, 04:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1