Data Set Analysis

[Guest]

I have a data set of numbers arranged in rows. They are 6 elements across.
Each data set is unique. There are many sets which are listed down the page.

I would like to see if there are common elements among the data sets. Ie 32
and 42 appear in 3 of the data sets, in row 1, 4 and 9. In Excel.


A B C D E F
1 32 42 49 56 29 12
2 20 38 47 54 35 86
3 1 8 13 4 12 23
4 13 18 32 46 42 56
5 15 20 24 55 3 99
6 37 38 45 54 21 89
7 35 36 43 52 5 57
8 5 48 50 53 4 65
9 1 32 50 42 4 12

 

[Duane Hookom]

Assuming your first column name is ID and the others are A, B,...F and the
table name is "tblMatrix".

Create a union query (quniMatrix) to normalize your data:
SELECT ID, A AS TheValue, "A" AS Col
FROM tblMatrix
UNION ALL
SELECT ID, B, "B"
FROM tblMatrix
UNION ALL
SELECT ID, C, "C"
FROM tblMatrix
UNION ALL
SELECT ID,D, "D"
FROM tblMatrix
UNION ALL
SELECT ID,E, "E"
FROM tblMatrix
UNION ALL SELECT ID, F, "F"
FROM tblMatrix;

Create a query based on quniMatrix:
SELECT quniMatrix.TheValue, quniMatrix_1.ID
FROM quniMatrix INNER JOIN quniMatrix AS quniMatrix_1
ON quniMatrix.TheValue = quniMatrix_1.TheValue
WHERE (((quniMatrix.ID)<>[quniMatrix_1].[ID]))
GROUP BY quniMatrix.TheValue, quniMatrix_1.ID
ORDER BY quniMatrix.TheValue;

This will return a result like:
TheValueID
1 3
1 9
4 3
4 8
4 9
5 7
5 8
12 1
12 3
12 9
13 3
13 4
20 2
20 5
32 1
32 4
32 9
35 2
35 7
38 2
38 6
42 1
42 4
42 9
50 8
50 9
54 2
54 6
56 1
56 4