Search and count problem

[GAC81]

Hello to every one, I'm new to this forum so excuse me if I'm posting in a wrong place.
I was trying to find function to do search and count in a example like this:

In one spreadsheet “A” I have combination of numbers (each number in one cell) like this

A B C D
1 1 7 17 20
2 3 6 12 13
3 5 10 11 17
…

in a 200 rows, something like lotto drawn numbers.

In the other spreadsheet “B” I have all three number combinations of 21 number (each number in one cell) like this:

A B C
1 1 2 3
2 1 2 4
3 1 2 5
…

and all that in 1300 rows.

Now I need function to count how may times each combination form sheet B appears in sheet A. Result should be written one cell to the right from the corresponding combination in sheet B
That should be some kind of compare each row from sheet B with each row in A and count if equal.

Problem is that numbers in sheet “A” are grouped by four in a row and sheet “B” three in a row.

Please point me is there any already written function for this kind of problem or may be I need to combine few of them.

[lecxe]

Hi

Assuming the first table in A!A1:D200 and the second table in B!A1:C1330, try in B!D1:

=SUM(--(MMULT(--ISNUMBER(MATCH(A!$A$1:$D$200,$A1:$C1,0)),--TRANSPOSE(COLUMN(A!$A$1:$D$200)>0))=COLUMNS($A1:$C1)))

This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

Copy down

HTH
lecxe

[DonkeyOte]

One way you might be able to do it is... if we assume per your sample that sheet A has numbers listed in ascending order from A:D then

'A'!E1: ="."&A1&"."&B1&"."&C1&"."&D1
copied down to E200 (ie all results)

You can then use this column in Sheet B

'B'!D1: =COUNTIF('A'!$E$1:$E$200,"*."&A1&"*."&B1&"*."&C1&".*")
copied down to D1330

[GAC81]

OK thanks for the solutions. I've chosen solution from DonkeyOte and after some time of usage I’ve noticed that formula can’t distinguish difference between 1 and 10, 2 and 20 and so on. So it is counting combination 20,11, 12 two times, once as 2, 11, 12 and other 20, 11, 12.

So I’m getting false combinations.

Is there any solution for this problem?

Thank you varry much.

[DonkeyOte]

Error on my part - you should double up the delimiter, eg:

Please Login or Register  to view this content.

then account for the above in the below:

Please Login or Register  to view this content.

does that work for you ?

(assumes values are listed in ascending order horizontally)

[GAC81]

Yes it works, thank you again for you quick answer.