+ Reply to Thread
Results 1 to 8 of 8

Find My Solution to my array

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Find My Solution to my array

    I posted a question last week and a wonderful person provided me with these two formulas to my problem:

    In Excel, it would be this on Sheet2:

    Cell A2 (array):
    =INDEX(Master1!$B$2:$B$12,MATCH(0,COUNTIF(Master1!$B$2:$B$12,"<"&Master1!$B$2:$B$12),0))

    Cell A3 (array):
    =IF(COUNTIF(Master1!$B$2:$B$12,">"&Master1!$B$2:$B$12),INDEX(Master1!$B$2:$B$12,MATCH(COUNTIF(Master1!$B$2:$B$12,"<="&A2),COUNTIF(Master1!$B$2:$B$122,"<"&Master1!$B$2:$B$12),0)),"")

    the plan was on my sheet 2 the array would search duplicated data on Master1 (sheet 1) and only add its to sheet 2 if and only if its is not already listed on sheet 2. so the first formula set up works great and so does cell A3 formula. However my two problems with A3 formula:1. the master1 has blanks (this is because its a working sheet and data is constantly being added) the second formula (A3) once i drag past all the data entered from master1 it just pulls it again but not all the data. Just chunks. I need the formula in the cell but no data if no data is listed.

    2. if i add temp data to master1 and "hide" it using conditional formatting it finds all the actual data and only some of the temp date. the array at this point to run can take up to 6 minutes and locks me out of excel, no good.

    if i can get so more assistance that would be great. i estimate that by the end of the year i will have over 2000 entries to calculate the array. so the array needs to be built to accommodate the amount just not all at one time, and does not require the user to drag down the formula past the 2000 row.

    i have a short mock up of data on a google doc here: https://docs.google.com/spreadsheets...it?usp=sharing

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,527

    Re: Find My Solution to my array

    2. if i add temp data to master1 and "hide" it using conditional formatting it finds all the actual data and only some of the temp date.
    I'm not sure how you hide data with conditional formatting, so just check out the attached and let me know how it works for you.
    Attached Files Attached Files
    Last edited by skywriter; 03-25-2015 at 02:08 AM.
    As a reminder, once your original request has been fulfilled please mark this thread as SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

    If you are happy with my help, then please consider clicking the add reputation button in the lower left hand corner of this post.

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Find My Solution to my array

    to hide data in conditional formatting you select the column or row and format only cell containing lets say TBD. All i do is change the font to white. its "hides" the TBD but still fills the cell. its a placeholder so when i want to enter new data it removes TBD in white and replaced its with actual data.

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,527

    Re: Find My Solution to my array

    I guess I don't get the logic. If you look in the formula bar you will see the TBD or whatever is in your cell. If you are going to have a "placeholder" that looks empty, why not just an empty cell? Why the extra overhead by putting conditional formatting into a cell? You speak of calculation time, remove the extra overhead. Conditional formatting is volatile.

    http://www.decisionmodels.com/calcsecretsi.htm


    This is from the page above.

    "Conditional Formats are volatile.

    Because conditional formats need to be evaluated at each calculation any formulae used in a conditional format is effectively volatile. Actually conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode, although VBA functions used in conditional formats will not trigger breakpoints when executed by the repaint."

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Find My Solution to my array

    So i am receiving 0 instead of data pulled from Master1. thoughts?

    as for conditional formating that was the only way my old forumla would actually pull the actual data. that is all.

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,527

    Re: Find My Solution to my array

    So i am receiving 0 instead of data pulled from Master1. thoughts?
    That's not very specific. That is all.

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Find My Solution to my array

    so i entered in your lovely formula from the excel file. i typed it into my working file. instead of a team name appearing on sheet 2 like it does on your excel file it spits out 0. and if i drag it all i get are zeros and not names. i must be missing something.

    =IFERROR(INDEX(Master1!$B$2:$B$12, MATCH(0,COUNTIF(Sheet2!$A$1:A10, Master1!$B$2:$B$12), 0)),"")

    i even tried it on your excel file and the result pulls zero not red raven
    Last edited by airedale360; 03-25-2015 at 02:34 PM.

  8. #8
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Find My Solution to my array

    Ok i fixed the 0 issues. i have to make my calculation manual in order for the formula to take. the sucky part is i have to do this to all 200 entries individually for 4 columns. Its a slow process. is there a way for automatic calculations to work?
    Last edited by airedale360; 03-25-2015 at 07:26 PM.

+ 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. Is an array the solution?
    By Hein in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 09:50 AM
  2. [SOLVED] Index array solution
    By holycowbanana85 in forum Excel General
    Replies: 15
    Last Post: 07-24-2012, 04:46 PM
  3. Replies: 4
    Last Post: 06-28-2011, 03:53 PM
  4. Index array formula the best solution?
    By edgearmy619 in forum Excel General
    Replies: 0
    Last Post: 05-18-2011, 02:40 AM
  5. Trying to find a faster solution than array formulas
    By travkliewer in forum Excel General
    Replies: 3
    Last Post: 09-09-2007, 04:12 AM

Tags for this Thread

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