Detect a subset in an Excel sheet

[Tarat]

Hi,

Perhaps someone out there has a clever solution to this question I have been stuck on for a while now?
I have a reference set of data in rows of ten numbers per row, like this where the first three rows are shown:

3,2,7,10,1,2,4,6,8,2
7,7,3,1,8,9,11,14,34,22
23,5,43,4,1,1,7,3,9,19
etc.

Here the numbers are comma separated for readability, but in the Excel file all numbers have their own cell.
Now I try to determine if a smaller set of data is a subset of the reference set. The smaller set has three numbers and is located on the same worksheet as the reference data. One example:

4,1,8

The smaller data set also use their own three cells.

Looking at row one of the reference data set, the smaller set in the example is a subset of reference data row 1. Other examples of subsets of row 1 are:

2,2,2
10,6,1

Examples of non-subsets are:

7,10,5 (The number '5' is not part of the reference data set's first row)
2,1,1 ( The number '1' only appears once in the reference data set's first row)

Any ideas?


Kind regards, HarryR

[HansV]

Welcome to Eileen's Lounge!

You could enter the following array formula, confirmed with Ctrl+Shift+Enter, in cell P7:

=AND(COUNTIF(B7:K7,M7:O7)>=COUNTIF(M7:O7,M7:O7))

This formula can be filled or copied down.

[Tarat]

Amazing!

Thanks a lot HansV, just what I was looking for. Have a great day!

Best regards, HarryR