# Find My Solution to my array

1. ## 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.

2. ## 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.

3. ## 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. ## 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. ## 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. ## 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. ## 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

8. ## 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?

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