Formula Challenge 014 - Count number of times a numeric array occurs in another

[Sajan]

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]

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.

 

[Luke M]

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!

 

[Sajan]

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.

 

[Sam Mathai Chacko]

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))))

 

[NARAYANK991]

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

 

[jeffreyweir]

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))

 

[jeffreyweir]

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

 

[Matt Louis]

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

 

[jeffreyweir]

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