Hi, I have a formula
{=IFERROR(INDEX($D$2:$D$5010,MATCH(0,COUNTIF(E2:$E$2,$D$2:$D$5010)+IF(COUNTIF($D$2:$D$5010,$D$2:$D$5010)>=1,0,1),0)),"")}
The formula looks at a named range OWNING and creates a unique distinct list from several duplicates.
Is there a way to do this without an array as it is slowing the sheet dramatically.
KEY :
The D range refers to list of entered duplicate names D10:D5010
E is the column the unique list is displayed in.
Thanks in advance
I'm not an expert in calcuation optimisation, though COUNTIF can be a little sluggish so you might want to test this alternative array formula with FREQUENCY:
=IFERROR(INDEX($D$2:$D$5010,SMALL(IF(FREQUENCY(IF($D$2:$D$5010<>"",MATCH($D$2:$D$5010,$D$2:$D$5010,0)),ROW($D$2:$D$5010)-MIN(ROW($D$2:$D$5010))+1),ROW($D$2:$D$5010)-MIN(ROW($D$2:$D$5010))+1),ROWS($1:1))),"")
Regards
Hello John Try below in e2 and drag down
non array
=IFERROR(INDEX($D$2:$D$5010,MATCH(0,INDEX(COUNTIF($E$1:E1,$D$2:$D$5010),0),0)),"")
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hi, Cant get it to work for some reason.
Last edited by john dalton; 03-12-2014 at 06:43 AM. Reason: grammar
For some reason that isn't enough information to help you further.
Regards
Brilliant, there is a zero appears in the cell underneath the last found name in the list anyway of stopping this happening?
Cheers
Perhaps you should clarify to whose solution you are referring?
Sorry XOR LX, I was referring to Hemesh's solution.
DO you have any blanks in between or does your range has empty cells at the end ?
Empty cells at the end.
don't take empty cells in the range
The list is a data entry list, is there a a way to limit the range if it finds the next empty cell and just calculates upto the the last actual entry point?
yes you can do the same try below
=IFERROR(INDEX($D$2:$D$7000,MATCH(0,INDEX(COUNTIF($F$1:F1,$D$2:INDEX($D$2:$D$5010,COUNTA($D$2:$D$7000))),0),0)),"") in e2 and drag down it will dynamically pick the last entry point up to 7000 rows. I think there are only text entries in Column D
What's slowing down your file is this:
+IF(COUNTIF($D$2:$D$5010,$D$2:$D$5010)>=1,0,1)
Not sure why it's there.
While the suggestion in post #3 is an improvement, it also has to be noted that simply adding another INDEX function to avoid array entry does not make the formula more efficient. What made the formula more efficient was removing the above IF function.
I see this all the time. There's a lot of misinformation on this site!
People are "deathly" afraid of using volatile functions and array formulas due to the spread of this misinformation.
Your mileage may vary!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Excellent words, Tony. Should be made a 'sticky' in my opinion.
I remember an OP once asking me if I could provide an alternative, non-array solution as he'd heard that array formulas could 'corrupt' a worksheet!!
I usually "hold my tongue" when I see these things as I don't want to start any arguments but today I just had to "snap out"!
Thanks guys I have got the formula working, some interesting varitaions for future sheets.
Thanks again.
JD
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks