• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Wrapping an unique distinct value array formula into an if function?

6

6tel

Member
Hello. Just wanted to share one concern for a calculation worksheet (named "calcula") that I'm setting. The question seems too long to explain, but actually is very simple:


At the "calcula" worksheet, I'm using a unique distinct formula (array formula) to get a list of unique distinct values that I need from another worksheet (named "data-offsite-radian6", column I). The formula goes like this:


{=INDEX('data-offsite-radian6'!$I$3:$I$1048576, MATCH(0, COUNTIF(calcula!$CA$7:CA8,'data-offsite-radian6'!$I$3:$I$1048576), 0))}


If you notice, I'm having a range covering the "I" column (in the "data-offsite-radian6" worksheet) from row 3 to row 1048576 (last row of the worksheet).


This is like this, because the "data-offsite-radian6" sheet will be increasing its data periodically, and I can't predict up to how much it will grow.


Since the prior formula takes too long to calculate on each cell of the "CA" column (in the "calcula" sheet), I thought about resuming the formula using a named range ("autor") for the "I" column (in the "data-offsite-radian6" worksheet), from row 3 to 1048576. The new formula looked like this:


{=INDEX(autor, MATCH(0, COUNTIF(calcula!$CA$7:CA7,autor), 0))}


But of course, I'm repeating the same issue. Excel takes too long to list unique distinct values on each cell of the "CA" column.


I tried reducing the extent of the "autor" range to rows between 3 and 1500 and it stills has a hard time indexing the unique distinct values that I need to extract…


I know the solution would be to reduce the "autor" name range to just the number of rows having data registry in the "I" column of the "data-offsite-radian6" worksheet, but then I would have to modify the named range on the name manager anytime I work on the file, and I'm not aiming at that. I'm looking forward to automate this unique distinct value listing task, and using a pivot table is not an option.


My colleague has suggested to find a way to get Excel recognize up to which row I'm having data on the I column, but I'm not sure on how to this… I could rely extending the formula on another data column that I have on the "data-offsite-radian6" worksheet (column D, "day"), but I am wondering if I can wrap the array formula posted before on an entire IF function… I'm new to array formulas, and a bit of a beginner to conditionals...


Can you guys please confirm if this is logical or possible? Just want my "calcula" worksheet to get a simple unique distinct value listing job without me having to update formulas, ranges, etc. anytime my data grows…


The file where these worksheets are is this (if you want to take a peek for better understanding):


https://www.dropbox.com/s/bupuamelzcpuqyn/exercise_to_chandoo.xlsx


Thanks in advance...
 
Ok, maybe the question was too long. I'll reformulate it:


What I would like to do is use the formula below for unique distinct values that I posted, but I need ranges to be, like, I don't know, dynamic, since data growing is perpetual for the column "I" at the data-offsite-radian6 worksheet...


At the formula below, I highlighted the data ranges that I would like to be dynamic or so...


{=INDEX('data-offsite-radian6'!$I$3:$I$1048576, MATCH(0, COUNTIF(calcula!$CA$7:CA8,'data-offsite-radian6'!$I$3:$I$1048576), 0))}


Please help... I'm tired and clueless...
 
Hi 6tel!


Create a nameRange

Name : AllData

Refers to :

Code: 
='data-offsite-radian6'!$I$2:INDEX('data-offsite-radian6'!$I:$I,COUNTA('data-offsite-radian6'!$I:$I))


Now in Unique List formula use Formula as

{=IFERROR(INDEX(AllData,MATCH(0,COUNTIF($CA$7:CA7,AllData),0)),"")}


It will work remarkable faster than the previous One.. :)


Regards,

Deb


EDIT: IfError has been added.. :)
 
Hi Deb. Thank you very much... I just discovered the offset function too. It seems interesting, but I'm testing it... I'll run your proposal first and let you know, since this has taken me the whole day... :-( I'm tired...
 
Hi Debraj.


Sorry to bother again with this... :-(


Just wanted to tell you that I tested your formula and seems it is very close to what I expect. It really works as a Ferrari on calculations, so thanks for that! But sadly for me it stills lacks the ability to dynamically expand the range on the "I" column when new rows of data are introduced... Being this the situation, I'm still forced to update the formula at the Name Manager anytime...


I've been trying to understand the structure of your formulas (specially the one at the "Refers to" in the "AllData" name at the Name Manager), but I'm not getting what CountA and Index function truly do... I mean, I sort of understand Index does quite the same as the Offset function for Dynamic Ranges, acting as a way to anchor and establish references for counting cells or so, and CountA avoids counting blank spaces... But then I'm seeing this concrete formula at the Name Manager is:


1.- Not expanding when new rows of data are introduced (where I think Index is supposed to act)


2.- Blank cells are counted (the formula is showing the blank space at the I3 cell in the data-offsite-radian6 worksheet... Maybe I'm interpreting it in the wrong sense...


So, I just wanted to appeal once again to your kindness and knowledge. I really feel umconfortable and ashamed having to ask for your help or anybody's help, but I'm still not as bright as you guys are...
 
Hi 6tel,


Sorry, for late response.. you know!!!.. Office.. Boss.. Project.. submit...Right Now... all this.. :)


Thanks for the kind words..


Above formula will work perfectly if you increase the number in Column I, and obviously AUTOMATICALLY.. I swear..


Hold.. with a condition, that there was no blank cell in between I2:I [LastCell].

If you check your I column.. there was 5 to 6 Blank cell, and my COUNTA formula ignore them.. just like me.. ha ha..


So you need to set your NamedRange, so that It directly just jump to the last used cell..

Please change the Name Range Refer to Section as below..


Code: 
='data-offsite-radian6'!$I$2:INDEX('data-offsite-radian6'!$I:$I,MATCH(REPT("z",255),'data-offsite-radian6'!$I:$I))


It will Auto Set Used Area In Column I..


Bye.. My MclarenF1 is waiting for me.. :)


Regards,

Deb..


PS:


If you fill blank area's with some "-" or anything.. My old Name Range will not disqualify from the 107 Rule :(
 
Hi, 6tel!

Give a look at this file:

https://dl.dropbox.com/u/60558749/Wrapping%20an%20unique%20distinct%20value%20array%20formula%20into%20an%20if%20function_%20-%20exercise_to_chandoo%20%28for%206tel%20at%20chandoo.org%29.xlsx

Where to look? Well, somewhere in the added worksheet... ;)

I'd bet you'll find it.

Regards!
 
Wow, wow, wow, Deb!


This new named range formula of your beats mr. Karivardhan and mr. Karthikeyan all together! Sadly, mines still are a bit like my folk F1 driver, mr. Maldonado, which I don't like and never will (and I'm talking about Maldonado, not my mediocre but still decent formulas, hehe!)


I'm documenting myself about these Match and Rept functions. With these sort of weapons in your pocket, you must be one of those hidden samurais here at Chandoo!


Don't worry about the blanks. Actually, I think it's better to have them counted, so these formulas work perfectly well...


And talking about the devil, I'm wondering why mr. Sir JB7 hasn't proposed your talent to an Excel Ninja Level. Why Pablo? Why? ;-)


Thank you guys very very much. You truly are gifted, wonderful and I like you. If coming to Colombia one day, please knock. I'd be more than happy to show you this country. :)


Greetings.
 
@Debraj Roy

Hi, my hidden Samurai!

Maybe we should ask Chandoo to add a new category badge, we have Key Master, Excel Ninja, Member, and why not Excel Samurai?

Been traveling a lot for two weeks, almost without time to dedicate to this site, hope to be back fully next week. BTW, luckily I wasn't missed neither a lot nor just a little, and that's good: it means that I could vanish in the air and nobody would notice it.

And about publicity -which I don't actually want, as I love current degree combination of publicity and anonimity-, I know that's hard to find friendly sites in spanish and of course none like this one, so why not give a Spanish-talking user other paths? It's always a pleasure to find other -known by me, of course- foreign non-English users like French or Brazilian ones. Some day maybe I learn Russian or Chinese, but only after Hindi :p

Nice to read you in Spanish.

Regards!


Hi, 6tel!

First of all, I love your nick!!! It's simple, D(eliberatedly) O(bfuscated) but not a lot, so hats off to you.

Regarding Debraj Roy's nomination to Excel Ninja Awards 2012 Spring (Southern hemisphere) I think that only recently -in fact, now- he'd be reaching the skills required to earn that badge... since I haven't read more than three words in Spanish from him before ;)

And about knocking at Colombian doors... why not? It's been always a targeted vacation country, but unluckily for the time being there are a lot of restrictions at Argentine that make a little harder (read as +30% expensive) to travel abroad.

Regards!
 
You must log in or register to reply here.
Back
Top