How can I transfer a compatibility matrix form excel ?

Hi all,

I have the following table as Input from excel sheet:

A1 A2 B1 B2

A1 0 0 0 1

A2 0 0 1 0

B1 0 1 0 0

B2 1 0 0 0

A1 and A2 are elements from Field A , B1 and B2 from Field B. I want to generate the following table:

A B

A1 B2

A2 B1

Any help please?

Thanks in advance.

[KSSV]

Try readtable.

Answer 1

[TED MOSBY]

Hi Amor,

You can simply use ‘readMatrix’ function and extract the relevant pairs where 1s appear as below:

inputTable = readmatrix('inputData.xlsx', 'Range', 'B2:E5');  % Adjust the range according to your data

% Field labels (rows and columns)
FieldA = {'A1', 'A2', 'B1', 'B2'};  % Corresponds to the rows
FieldB = {'A1', 'A2', 'B1', 'B2'};  % Corresponds to the columns

% Find the indices of all ones in the input table
[rowIdx, colIdx] = find(inputTable == 1);% finding indices which have 1s in the table
relevantRows = rowIdx <= 2;  % We are only interested in A1, A2 rows
rowIdx = rowIdx(relevantRows);% Passing the relevant rows
colIdx = colIdx(relevantRows);

% Create the output table 
outputTable = table(FieldA(rowIdx)', FieldB(colIdx)', 'VariableNames', {'A', 'B'});
disp(outputTable);

Hope this helps!