# Extract rows from cell array based on two values in two columns and reassign them to a new variable

1 view (last 30 days)
So I have a clunky problem I have trying to solve (as seen by the title).
Essentially I have a very large Excel file and I need to create new variables based on two values, one in column 4 and one 5.
Column 4 contains the values 1-3, and Column 5 contains the values 1-6. This creates a large number of combinations, and while I think I know how to write out the code, at my level of programming knowledge it is rather clunky.
For example I am looking to take all of the cases where I say have Column 4-Value 1 and Column 5-Value 1
So my idea was to write:
for i = 1:length(Excel)
if isequal(Excel{i,4},1) && isequal(Excel{i,5},1)
NewVariable_1_1{i} = Excel(i,:)
end
end
And so on and so forth until I've written every possible combination.
This obviously is very inefficient and clunky, as I'd have to write a new line for Column 4-Value 2 and Column 5-Value 1, Column 4-Value 2 and Column 5-Value 2, etc etc.
Beyond solving this specific problem, I would like to expand my knowledge to be able to write more robust and efficient code if I ever encounter this situation.
Any help that could be offered would be much appreciated!

David Hill on 8 Nov 2019
Please provide a sample of excel file.
Korbinian Volgt on 8 Nov 2019
Yes an example would be great, also it's not clear what will be the value you want to give depending on column 4 and 5. For your problem with the combinations, maybe a switch case can make it more readble. Make sure to put also default values, in case someone puts in a number that is not inside your range
for i = 1:length(Excel)
Val4 = Excel{i,4};
Val5 = Excel{i,5};
switch Val4
case 1
switch Val5
case 1
NewVariable{i} = Excel{i,7};
case 2
NewVariable{i} = Excel{i,8};
case 3
NewVariable{i} = Excel{i,9};
case 4
NewVariable{i} = Excel{i,10};
case 5
NewVariable{i} = Excel{i,11};
case 6
NewVariable{i} = Excel{i,12};
otherwise
NewVariable{i} = -1;
end
case 2
switch Val5
case 1
NewVariable{i} = Excel{i,13};
...
end
end
end
Jonathan Marchetto on 11 Nov 2019
Hello, thanks for getting back to me.
I attached a sample excel that gives an idea of the data I am working with and the layout.
The code I came up with initially works but was rather clunky to write out.
Essentially it creates 18 new variables from the data in the main excel file that contain rows in which both statements are true.
E.g. when a row contains value 1 from group 1 and value 1 from group 2, it stores it in the new variable 1.
But I can't help feeling there is a better way to write it.