CODEWITHSUNDEEP
excel
tkoz_dk
tkoz_dk

Reputation: 239

Use Row and Column header to determine combinations in Excel

My data consist of a matrix of 1 and 0 that denote a connection.

I need to get all combinations of connections as a list. Sample data looks like this

      Head Foot Hand
Head    0    1    0
Foot    1    0    0
Hand    0    0    1

So there is a connection between head & foot, foot & head and hand & hand.

So I need an output that simply states these connections as a list:

Head, Foot
Foot, Head
Hand, Hand

Upvotes: 1

Views: 202

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

You can create a matrix below your input data according to this sample:

enter image description here

The cell C6 has this formula:

=IF(C2 = 1, CONCATENATE($A2, ", ", C$1), "")

You can simply copy this result matrix to a text editor to obtain a list. If you want a solution entirely in Excel, you should look into Pivot Tables.

Upvotes: 1

Related Questions