Friday, May 6, 2011

yogi_Count Objects In An Array Tthat Match Another Array In Different Size

Yogi Anand, D.Eng, P.E.                                 Google Spreadsheet                    www.energyefficientbuild.com
Santiago said:
Count objects in an array that match another array in different size.
I'm working on a dashboard and I have 2 arrays. One with unique values and another one with many values that match the first array. I want to know how many values in the second array match the values in the first array. I know how to do this on VBA, but I'm not allow to use it.
Here is the example:
Array1={a,b,c}
Array2={a,a,a,r,t,y,c,b,b}
here, the result of what I want would be 6 (3 a, 2 b and 1 c)
I appreciate any help.
reference:
http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=53050779&gid=3124035&commentID=38525014&goback=%2Egde_3124035_member_52807150&trk=NUS_DIG_DISC_Q-ucg_mr#commentID_38525014
------------------------------------------
Following is a solution is Sheet1 using Google Docs Spreadsheet