Can I take 2 columns of matching data to generate a matrix

[fletcherj63]

I'm analysing DNA matches and want to use a program that will cluster my matches. The program will accept an excel sheet with the data organised as shown on the right.

I can easily generate a table as shown on the left, where A matches B, C and D; B matches A and D; and C matches A. Is there anyway to generate the table on the right from the table on the left?
Thanks for any help.

 

[StephenCrump]

Welcome to the Forum!

Here's one way:

	A	B	C	D	E	F	G	H	I
1	Match 1	Match 2
2	A	B			A	B	C	D
3	A	C		A		1	1	1
4	A	D		B	1			1
5	B	A		C	1
6	B	D		D	1	1
7	C	A
8
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TRANSPOSE(D3#)
D3:D6	D3	=SORT(UNIQUE(TOCOL(A2:B7)))
E3:H6	E3	=IF(COUNTIFS(A2:A7,D3#,B2:B7,E2#)+COUNTIFS(B2:B7,D3#,A2:A7,E2#),1,"")
Dynamic array formulas.

 

[fletcherj63]

Welcome to the Forum!

Here's one way:

	A	B	C	D	E	F	G	H	I
1	Match 1	Match 2
2	A	B			A	B	C	D
3	A	C		A		1	1	1
4	A	D		B	1			1
5	B	A		C	1
6	B	D		D	1	1
7	C	A
8
Sheet1

Cell Formulas
Range	Formula
E2:H2	E2	=TRANSPOSE(D3#)
D3:D6	D3	=SORT(UNIQUE(TOCOL(A2:B7)))
E3:H6	E3	=IF(COUNTIFS(A2:A7,D3#,B2:B7,E2#)+COUNTIFS(B2:B7,D3#,A2:A7,E2#),1,"")
Dynamic array formulas.

Thank you very much!!