Determine no. of intercept & union 1

[gohym]

Hi,

I badly need some help here...

I have an excel worksheet with 33 records and 15 fields. For each pair of records, I need to determine the number of fields in which they have an exact match of value (intercept) and the no. of fields when the each pair is in union. Let me give an example...

Record R1 has fields A, C, and D. The corresponding values of the fields are a7, c3 and d9. record R2 has fields A, B, D and E. Corresponding values of a7, b1, d4 and e6.

i.e. R1: {a7, c3, d9)
R2: {a7, b1, d9, e6}

R1 intercept R2 will be 2 (a7=a7 and d9=d9)
R1 union R2 will be 5 (a7, b1, c3, d9, e6)

The results should be tabulated in 2 symmetrical cross-tables (one for intercept and the other for union) with the rows and columns made up of the record IDs. The content of the cross-table will the intercept/union value.

I will be doing this for quite a bit of data later on, thus I tot it will be crazy to do it manually... but I have no idea as to how to begin. Can someone please give me some pseudo code and general guidance? Should I attach the code to a command button or can I create some kind of formula in the cells of the symmetrical table?

Thanks!!

 

[SkipVought]

Hi,

Would you ever have the case...

R1: {a7, c3, d9)
R2: {a7, b1, d99, e6}

where the values in the same column are not identical (ie COLUMN D)

And if so, what to do.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Assuming your data as follows:-

A3:A35 having labels R1, R2, R3, R4, R5 etc
Data, eg your a3, b1, c6, etc being in the range B335

For the INTERSECTS:-

Set up a 33 x 33 table to receive the values, eg A38:AH71, with R1, R2, R3, R4, R5 etc in both ranges A39:A71 AND B38:AH38.

In cell B39 put the following formula and array enter it using CTRL+SHIFT+ENTER:-

=IF($A39=B$38,"",SUM(IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),,,15)=OFFSET($A$2,MATCH(B$38,$A$39:$A$71,0),,,15),1,0)))

Then copy the cell and paste it to C39:AH39. Then paste it to B40:AH40. You cannot paste all in one op because it is an array and it will not let you change what is in B39, so you will get the message 'you cannot change part of an array' if you try.

This should now give you all your intersects.

For your UNIONS:-

Set up a 33 x 33 table to receive the values, say A74:AH107. Again, put R1, R2, R3 etc in the ranges A75:A107 and B74:AH74.

In cell B75 put the following formula:-

=IF($A75=B$74,"",30-B39)

and then just copy and paste to B75:AH107

This assumes that you have no blank fields, but if this is not the case then post back.

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Also assumed your data was in the following format:-

R1 a7 b8 c2 d3 e1 f8 g2 h6 i1 j4 k9 l6 m7 n7 o3
R2 a3 b7 c8 d5 e9 f2 g2 h5 i2 j8 k4 l1 m2 n3 o2
R3 a7 b3 c3 d6 e5 f8 g8 h3 i2 j4 k3 l8 m1 n9 o2
R4 a2 b8 c4 d6 e7 f9 g4 h5 i8 j1 k4 l1 m5 n7 o7
R5 a5 b8 c2 d8 e1 f5 g7 h1 i5 j2 k5 l3 m7 n9 o8
R6 a4 b2 c9 d3 e8 f9 g7 h3 i3 j7 k7 l3 m9 n5 o4
R7 a2 b5 c9 d8 e3 f5 g6 h1 i4 j6 k7 l5 m6 n9 o5
R8 a8 b4 c5 d1 e3 f1 g2 h9 i1 j5 k6 l1 m5 n9 o1
R9 a5 b8 c4 d8 e5 f2 g8 h3 i9 j9 k2 l4 m9 n6 o4
R10 a4 b3 c6 d6 e1 f7 g6 h2 i1 j7 k9 l6 m8 n8 o7

Note that I have also assumed all a's are in the same field, b's in the same field, c's in the same field etc

If not, then please clarify with the exact format of the data, whether or not there can be blanks etc.

Regards
Ken..............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[KenWright]

Doh!!! - Missed out the Offset column reference of 1 - Amend the first large formula in my note that I said to put in cell B39 to the following please:-

=IF($A39=B$38,"",SUM(IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15)=OFFSET($A$2,MATCH(B$38,$A$39:$A$71,0),1,,15),1,0)))

Rest is as stated

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

Hi guys... thanks for the posts.

To SkipVought: If values of the same column are not identical... for intercept, d9 and d99 are not counted as intercept (i.e. no count); for union both will be added as an count each.
for your e.g.
R1: {a7, c3, d9)
R2: {a7, b1, d99, e6}

Intercept= 1 (a7=a7)
Union= 6 (a7, b1, c3, d9, d99, e6)

To KenWright: Cheers mate! will digest your posts, give the stuff a try and get back to you! Thanks!!

 

[gohym]

KenWright: There are quite a bit of blanks. Does it matter?

 

[KenWright]

That depends on where the blanks are. Can you post a snapshot of some of the data as I did in my example. What I'm really looking for is to see if your data looks like:-

R1 a7 b3 c4 d9 e5 f4 g7 h1 i1 j6 k2 l6 m1 n6 o1
R2 a8 a7 c3 d7 e7 f8 g9 h5 i9 j8 k8 l3 m8 n4 o9
R3 a8 b5 c9 d9 e5 f6 g8 h6 i6 j8 k9 l5 m5 n6 o8
R4 a3 b7 c8 d9 e2 f7 g3 h6 i2 j2 k5 l4 m1 n8 o6
R5 a9 b5 c5 d9 e5 f5 g3 h6 i2 j8 k4 l6 m3 n5 o9
R6 a3 b3 c9 d1 e4 f7 g1 h6 i9 j6 k2 l6 m6 n8 o8
R7 a2 b5 c4 d9 e7 f8 g3 h6 i9 j8 k3 l7 m8 n3 o2

or like:-

R1 a7 b4 c5 d1 f5 g9 i1 j8 k4 m4 n4 o8
R2 a8 c2 e6 f2 g7 i2 j8 k6 m8 o2
R3 a9 b6 c2 d3 f8 g8 h8 i4 j4 k8 l9 m3 n9 o1
R4 a4 c3 e8 g8 i4 k4 m3 o1
R5 a6 b8 d1 f1 h7 i2 j9 k1 l4 m8 n9 o5
R6 a8 c1 d2 f6 g3 h1 j7 k3 m1 o3

or like:-

R1 a7 b4 c5 d1 f5 g9 i1 j8 k4 m4 n4 o8
R2 a8 c2 e6 f2 g7 i2 j8 k6 m8 o2
R3 a9 b6 c2 d3 f8 g8 h8 i4 j4 k8 l9 m3 n9 o1
R4 a4 c3 e8 g8 i4 k4 m3 o1
R5 a6 b8 d1 f1 h7 i2 j9 k1 l4 m8 n9 o5
R6 a8 c1 d2 f6 g3 h1 j7 k3 m1 o3
Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

The data is more like your second set...

R1 a7 b4 c5 d1 f5 g9 i1 j8 k4 m4 n4 o8
R2 a8 c2 e6 f2 g7 i2 j8 k6 m8 o2
R3 a9 b6 c2 d3 f8 g8 h8 i4 j4 k8 l9 m3 n9 o1
R4 a4 c3 e8 g8 i4 k4 m3 o1
R5 a6 b8 d1 f1 h7 i2 j9 k1 l4 m8 n9 o5
R6 a8 c1 d2 f6 g3 h1 j7 k3 m1 o3


Really appreciate your help.

 

[KenWright]

Try replacing the large formula in B39 with the following and see if that does it:-

=IF($A39=B$38,&quot;&quot;,SUM(IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15)<>&quot;&quot;,IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15)=OFFSET($A$2,MATCH(B$38,$A$39:$A$71,0),1,,15),1,0))))

Will come back on the second table of unions, as blanks in the equation throw that into disarray.

By the way, when you say 'looks like' my second table, all a's are in the same column, b's are in the same column etc, with blanks in various cells.

Regards
Ken...................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

Yup, all a's are in the same column (etc) and if the particular record (e.g. R1) do not have values for field A, then it is left blank.

e.g. R1 = {a1, , c2, , e7, ...)

i.e. R1 has no values for field B and D.

Thanks.

 

[KenWright]

OK, what I gave you should work then, and for the Unions, you can use the following formula in cell B75 instead of the previous one I gave you:-

=IF($A75=B$74,&quot;&quot;,30-(COUNTIF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15),&quot;&quot+COUNTIF(OFFSET($A$2,MATCH(B$38,$A$39:$A$71,0),1,,15),&quot;&quot)-B39)

(Not an array formula, so enter normally)

and then just copy and paste to B75:AH107

If you want a working copy of the file, then just let me know and I will mail you one

Regards
Ken....................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

I realised that blank cells and blank cells are deemed to be intercepts... can the formula be modified such that blank cells are left out of the calculation? I should have said this earlier... sorry.

Currently...
R1 = {a1, , c2, , e7}
R2 = {a1, , c4, , }
Intercept = 3 (a1=a1; &quot;&quot;=&quot;&quot;; &quot;&quot;=&quot;&quot

But in this case what I wanted is
Intercept = 1 (a1=a1)

Thanks...

 

[KenWright]

That's what the formulas should be giving you. As long as you picked up the changes when I said use the following formula in B39 with:-

=IF($A39=B$38,&quot;&quot;,SUM(IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15)<>&quot;&quot;,IF(OFFSET($A$2,MATCH($A39,$A$39:$A$71,0),1,,15)=OFFSET($A$2,MATCH(B$38,$A$39:$A$71,0),1,,15),1,0))))

This ignores Blanks where either the cell is really empty, OR, the cell has a value of &quot;&quot; returned by a formula, eg =IF(A1=&quot;&quot;,&quot;&quot;,&quot;zzz&quot.

I have a working file with some checksums on it that all pan out just fine this end with those formulas.

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

I'll email you to get the file. Thank you!!

 

[KenWright]

OK:-

ken.wright at ntlworld.com

Regards
Ken...............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[gohym]

Hey Ken,

Everything is working perfectly now and I think I learned quite a bit of stuff by working through the file you gave me. Thank you!!

Cheers!

 

[KenWright]

My pleasure. It's nice to be able to give a solution, but so much more satisfying when somebody learns something from what you have done, so I'm glad you found the file useful.

Hopefully that's saved you a fair bit of time as well.

Regards
Ken.............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------