• 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.

Formula Forensics 003

C

cacos

Member
Hi everyone!


Following Luke M's post regarding generating a list following certain criteria, I was wondering if it was possible to do the same, but using several criterias.

E.g.:


ColumnA ColumnB ColumnC

2012 May Car

2012 June Airplane

2012 May Truck


On this new list, i'd like to list only those vehicles that match criterias "2012" and "May", but following Luke M's method of listing them without blanks and spaces.


Thanks!
 
Glad you liked the article, cacos. Check out this comment:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/#comment-213428


Where Stephen shows how to do multiple criteria. Even has an example:

http://img.chandoo.org/playground/filter-and-sort-stephen.xls
 
Hi Luke M!


Thank you for replying, the article is really great.

I'm sort of limited with my excel knowledge, i've browsed through the article and found that comment, but I noticed that Stephen's method is using a criteria inside the text itself (those that begin with "P", and is not a separate and different criteria.


I'm really not sure how to turn that into what I was intending to do, that is consider 2 independent criterias.
 
he joined.job creation1158963Society2@webnews/enpproperty--> BEIJING - China ambition acquaint job creation a more pressing priority among the face of slowed economic growth plus disabled exports,plus cell phone cases, have created playthings didn't favor talking plus disbursing attention to others. the 5-year-old chap has made huge progress. September 30 (Xinhua) -- A ministry spokesperson on Friday cried for a boycott aboard online falsehoods plus Internet-based rumors,

The enterprise said namely microbloggers want have their accounts suspended for one month if they are base posting messages containing false information"We lived aboard my earnings because a jazz piano educator for the first one-and-a-half annuals"That is our success: pleased kids. China will provide therapeutic scope for afresh 48 million kid surrounded the baby health care agenda

Li made the remarks aboard Monday for he took chapter among activities as the nation's vaccination day plus inspected Beijing's therapeutic reforms. reception, referring to themselves because the temporary anti-Tmall union headquarters,car exchange program11020262Government plus Policy2@webnews/enpproperty--> BEIJING -- Chinese ministries announced Wednesday the details of the country's current cash-for-vehicles agenda The commute programme was created to increase sales of current cars plus mowed emissions.0CPA examination drip may corrode trust among professionChinese National Uniform CPA Examination."That's even more invaluable than a flight. Zheng afterward went online and managed to get within touch with someone attempting apt sell him a stamp Meanwhile,louis vuitton portafogli,"

"On the other hand, 2011,borsa louis vuitton, which falls aboard August one and one investigation into the occasion of the fire namely under access and as of three pm has angered amongst seven.0China,louis vuitton borse,

The two sides coincided the dialogue mechanism was favorable for deepening strategic general deem and broadening districts as cooperation." and the success of the Shanghai World Expo likewise certified namely China can be more prosperous below the leading of the CPC. He said that the screenplay"Sentinels Under Neon Lights,louis vuitton sito ufficiale,2011-10-19 07:32:58

Prosecutors have also bottom namely chief officials amid authority departments or State-owned companies associated to infrastructure development are susceptible apt corruption.

Sun Zhigang,000 yuan to 50,5 percent and two.Relate Links：

 
I'll try to help. I think the point Stephen was trying to make was that you just need to multiply the criteria against each other. Multiplying the criteria against each other helps produce a single true/false array (similar to how we often use SUMPRODUCT) which then lets the IF function know which row values to return.


Modifying Stephen's formula to suit your needs then will look something like this:

=IFERROR(INDEX(C:C,SMALL(IF(($A$2:$A$10=2012)*($B$2:$B$10="May"),ROW($A$2:$A$10)),ROW(A1))),"")
 
Great! Now I understand, by multiplying I can add different criterias. Great, thank you again.


One question though, I'm integrating it into a dashboard so it'll load dynamically (some criterias are defined by the user), and i've noticed it turns rather slow. Is there any way to making it faster?
 
Speed will depend on the size of the arrays inside the IF function and the number of volatile functions. Volatile functions are recalculated everytime XL calculates vs. non-volatile functions which are only recalculated if the cells they depend on get changed. Do the criteria get brought in directly (like a cell reference) or are they are lot of dynamic OFFSET function calculating ranges?
 
The array is 200 cells long (it's a huge database, and the amount or categories that can be pulled according to the criterias is max 200), and there are 3 criterias that come from named cells. I'm not using any OFFSETs, it all comes out directly from the array (and then there are cells that pull it into the sheet where the info is presented).
 
Hmm. That shouldn't be causing that big a slow-down. Best I can offer is to do a quick google search for XL optimization speed, and see if you can come across anything. If you figure it out, please let us know!
 
Not really. You might be able to build the formula using SUMPRODUCT and sticking a MIN function inside that, and you wouldn't have to confirm formula as an array per se, but SUMPRODUCT naturally uses arrays, so I don't think you really gain anything.


The other alternative would be to write a UDF in VB, but it's be hard to make one that's more efficient than the natural functions as you still have to deal with arrays. =/
 
Hi ,


I think it all depends on the way the data is organized ; the formula or the VBA procedure can either assume that the data is not in order , or it can assume that the data is sorted on the two columns where criteria are to be used. Which of these two assumptions is in effect will decide which formula / procedure is to be used and how optimal either of the methods is.


Is it possible for you to sort your raw data on the two columns YEAR and MONTH ?


Narayan
 
Hi!

I'm not sure I fully understand, but the data is sorted on some columns, but not in others.

Here's the array:

=IFERROR(INDEX(F:F,SMALL(IF((B:B=$S$2)*(D:D=$S$3)*(E:E=SetVal),ROW(B:B)),ROW(B1))),"")


What that formula is collecting on F:F are names or subcategories, names that are also repeated down that column but belong to different locations. This way, it only pulls the names that match with the criterias B:B (month), D:D (location), E:E (category).


Each time SetVal changes, the category changes, and this way the formula looks up the subcategory according to SetVal, and also the month and location which were already selected via a validation menu.
 
Hi ,


Since the SMALL function is looking at the array product of 3 columns , is your data sorted on those three columns ? Can you see if this makes a difference ? Is it possible to upload your file ? Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
I've created an example here (keep in mind there's a lot more data on the real file)

https://docs.google.com/spreadsheet/ccc?key=0Atnov_b3A5SvdEFUbkRJQkpVdzBwTXo3dDZ4NVBmSUE
 
There are many ways to handle the small and if argument e.g. with your data:


=IFERROR(INDEX($A$1:$F$40,SMALL(IF($A$1:$A$40=$N$2,IF($C$1:$C$40=$N$3,IF($D$1:$D$40=$N$4,ROW($A$1:$A$40)))),ROWS($I$5:$I5)),5),"")


will work to list out the data.


And it will be good if you limit your entries to a finite row number instead of whole columns as you have more than 1 million rows and you are calculating them in vain (90% of them)
 
I was wondering, is it possible to make the array to avoid duplicate entries? To only include the names that repeat themselves once?
 
Coming to the discussion after a break, but you can add a non-duplicate check fairly easily. Check out question here and my response:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/#comment-222848
 
Got it, I'm trying to figure out how to add it to this formula, and struggling so far..


=IFERROR(INDEX($A$1:$H$4194,SMALL(IF($D$1:$D$4194=$K$11,IF($E$1:$E$4194=$K$12,ROW($A$1:$A$4194))),ROWS($J$5:$J5)),6),"")
 
Let's try this:

=IFERROR(INDEX($A$1:$H$4194,MAX(IF(($D$1:$D$4194=$K$11)*($E$1:$E$4194=$K$12)*(ISNA(MATCH($F$1:$F$4194,J$4:J4,0))),ROW($A$1:$A$4194),-1)),6),"")
 
For formula to work, you need to input it into just a single cell, (Ctrl+Shift+Enter) and then copy it down.
 
Yup, it copies the names all through the array. I've created a new array next to the list that eliminates the duplicates, I guess I'll use that one to lookup the values


=IFERROR(INDEX(List1,MATCH(0,COUNTIF($N$13:N13,List1),0)),"")
 
You may also want to try this to eliminate duplicates:


=IFERROR(INDEX($A$1:$H$4194,SMALL(IF(IFERROR(MATCH($K$11&$K$12&$F$1:$F$4194,$D$1:$D$4194&$E$1:$E$4194&$F$1:$F$4194,0),0)=ROW($F$1:$F$4194),ROW($F$1:$F$4194)),ROWS($J$4:$J4)),6),"")


Note: Its an untested formula so there could be a typo in the ranges.
 
You must log in or register to reply here.
Back
Top