• 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 Challenge 014 - Count number of times a numeric array occurs in another

Sajan

Excel Ninja
Challenge Name

-- Count the number of times a numeric 2-d array occurs in another 2-d array


Challenge Description

-- This is a formula challenge. As such, only formula submissions are accepted.

-- For two lists named "nList" and "sList", count the number of times "sList" appears in "nList".

-- To keep things simple, we will assume that both nList and sList have two columns each

-- Also, to simplify things, we will assume that sList has exactly two rows

-- Also, to simplify things, assume that sList will have at least one non-zero value

-- sList and nList may be located anywhere on a worksheet

-- nList has more rows than sList

-- nList and sList contain values that are not in any sequence

-- Goal is to calculate the count without using any helper cells.


We will remove some of these assumptions in a future challenge that tackles a more general case.


Sample data and output:

nList

[pre]
Code:
1	2
3	4
5	6
7	8
3	4
7	6
3	4
5	6
1	0
2	3
sList[/b]

3	4
5	6
[/pre]
In this case, the output expected is 2
since sList occurs twice
in nList.


Enjoy!

-Sajan.
 
Somehow, I have a feeling this requires the use of the mystical MMULT function, or which I have little understanding. My attempt to summon the dark arts...

=MMULT(MMULT(1,nList,sList)+INDIRECT("Eye of newt"),TEXT(101,-PI()^SQRT(-1)))


Rats, no good...looking forward to seeing what some others come up with.

Happy Friday everyone!
 
Hi Luke,

I think a mixture of the following will also help!

Eye of newt, and toe of frog,

Wool of bat, and tongue of dog,

Adder's fork, and blind-worm's sting,

Lizard's leg, and howlet's wing


But really, I think this is easier than what you are picturing! :)


-Sajan.
 
Here's a hard coded bazooka


=SUM((OFFSET(sList,,,1,1)&OFFSET(sList,,1,1,1)=TRANSPOSE(OFFSET(nList,,,,1)&OFFSET(nList,,1,,1)))*(OFFSET(sList,1,,1,1)&OFFSET(sList,1,1,1,1)=TRANSPOSE(OFFSET(nList,1,,,1)&OFFSET(nList,1,1,,1))))
 
Hi Sajan ,


Similar to Sam's , hard-coded :


=SUM(--((MMULT(IF((OFFSET(nList,,,ROWS(nList)-1)=(INDEX(sList,1,0))),1,),{1;1})+MMULT(IF((OFFSET(nList,1,,ROWS(nList)-1)=(INDEX(sList,2,0))),1,),{1;1}))=4))


Narayan
 
Without any peeking whatsoever, here's mine:


=SUM(N(MMULT(N(nList&"|"&OFFSET(nList,1,)=OFFSET(sList,,,1)&"|"&OFFSET(sList,1,,1)),{1;1})=2))


Or even shorter using INDEX:

=SUM(N(MMULT(N(nList&"|"&OFFSET(nList,1,)=INDEX(sList,1,)&"|"&INDEX(sList,2,)),{1;1})=2))
 
I should point out that my above formula only handles data that is 2 columns wide.


Here's a formula that handles any number of columns (although it still only handles a lookup list that is 2 rows deep)

=SUM(N(MMULT((INDEX(sList,1,)=nList)*(INDEX(sList,2,)=OFFSET(nList,1,)),TRANSPOSE(COLUMN(sList))^0)>1))


Edit: Whoops, I think I've preempted Sajan's 2nd iteration of this challenge. Sorry 'bout that.


Anyway, I better get started on his likely 3rd iteration, where both data sets share the same about of columns but the column width is > 2, AND sList can be any number of rows. (Don't worry, won't post it here until a challenge is officially called).


Good challenge Sajan
 
=SUM((D2&E2=A1:A11&B1:B11)*(D3&E3=A2:A12&B2:B12))


This works for me, yielding the same array and result as previous respondents' formulas. Assumes nList is A2:B11 and sList is D2:E3.


However, its relative simplicity makes me think I'm overlooking something...
 
Matt, this misses the requirement that sList and nList may be located anywhere on a worksheet. i.e. we're not allowed to hard-code the address where the lists are into the formula
 
Back
Top