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.

Luke M

Excel Ninja
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

Excel Ninja
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

Active Member
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

Excel Ninja
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

Active Member
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

Active Member
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)&#62;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 &#62; 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

New Member
=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

Active Member
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