I'm trying to select out rows with strings from a spreadsheet that agree with certain conditions.

Hi,

I'm trying to write a code to select out the strings that have a 1 in the corresponding column. This is the code I wrote:

X = xlsread('stringspreadsheet.xlsx', 1, 'B:B'); %Choose row
strings= xlsread('stringspreadsheet.xlsx', 1, 'A:A'); %Choose row

stringsRule1= strings(X>0);

Any help would be greatly appreciated.

Best, A

Answer 1

[User_in_Gim]

So, on your two row, you want two have 2 variables, the first one fill with the title, and the second one with your data ?

You can do it with the following code :

 [Data,Title] = xlsread(your_file,1,'A1:B28');

[Alexandra Brian]

I'm trying to return the following data from the spreadsheet attached, so I can write it into a different page:

A B C D E F G H I J K L M N O P Q R

The above are all the letters from stringspreadsheet.xlsx in rows where X > 0

[User_in_Gim]

To complete my answer, the previous line does the job when you want to extract data where the first row is a string and all the others rows are numbers. So in your example, it is slightly different.

So depending your needs, you can use that : But be carefull with your data type

[~,First_column] = xlsread('stringspreadsheet.xlsx',1,'A1:A28');
Title(1)= First_column(1);  % Get the first row
First_column_data = First_column(2:28); % Get the data from the first column
clear First_column;
[Second_column_data,Title(2)] = xlsread('stringspreadsheet.xlsx',1,'B:B');
Data = [First_column_data,num2cell(Second_column_data)]; % Matrix with both vectors
all =[Title;Data]; % all extract

Now you can extract all of your data and you can compare for each row if Second_column_data >0

[Alexandra Brian]

Hey, Thanks a lot that was really helpful. I wrote the following code from your example, but MATLAB returned an error following my code:

[~,First_column] = xlsread('conspreadsheet.xlsx',1,'A1:A8088');
Title(1)= First_column(1);  % Get the first row
First_column_data = First_column(:); % Get the data from the first column
clear First_column;
A = xlsread('conspreadsheet.xlsx', 1, 'F:F'); %Choose row
B = xlsread('conspreadsheet.xlsx', 1, 'G:G'); %Choose row
C = xlsread('conspreadsheet.xlsx', 1, 'H:H'); %Choose row
D = xlsread('conspreadsheet.xlsx', 1, 'I:I'); %Choose row
E = xlsread('conspreadsheet.xlsx', 1, 'J:J'); %Choose row
F = xlsread('conspreadsheet.xlsx', 1, 'D:D');%Choose row
Data = [First_column_data,num2cell(A),num2cell(B), num2cell(C), num2cell(D), num2cell(E)]; % Matrix with both vectors

G = xlsread('conspreadsheet.xlsx', 4, 'E:E');%Choose row

*Error using horzcat Dimensions of matrices being concatenated are not consistent. *

[User_in_Gim]

Just look at your workspace and you will see 2 mistakes. Your vectors B, C and F are 8087*1 while First_column_data is a 8088 *1 vector.

When you open this variable, we can see that First_column_datat first row is the title and the others rows are datas. This is why, I wrote :

First_column_data = First_column(2:28); % Get the data from the first column

Now on your code you can use this :

First_column_data = First_column(2:length(First_column)); % Get the data from the first column

Moreover, be careful with matrices D and E which are empty.

You don't need to write xlsread('conspreadsheet.xlsx', 1, 'H:H'); for each column, if you just want to read numbers.

See following code :

clear all;
clc;

[~,First_column] = xlsread('test.xlsx',1,'A1:A8088');
Title(1)= First_column(1);  % Get the first row 
First_column_data = First_column(2:length(First_column)); % Get the data from the first column
[Data,Title2] = xlsread('test.xlsx', 1, 'B1:H8088'); % Extract all your number data and title 
Data = [First_column_data,num2cell(Data)]; % Concatenate your data
Title = [Title(1),Title2];                 % Concatenate your title
clear First_column Title2 First_column_data;

I hope it will help you.

Answer 2

[Alexandra Brian]

Thank you Gim! Now I just need to figure out how to select column 1's value based on the other columns.

[Alexandra Brian]

Hey Gim,

When I tried to sort through the cells with values as numbers, I was returned an error. How do I keep the data in the columns besides column 1 as numbers?

clear all;
clc;
[~,First_column] = xlsread('conspreadsheet.xlsx',4,'A1:A8088');
Title(1)= First_column(1);  % Get the first row 
First_column_data = First_column(2:length(First_column)); % Get the data from the first column
[Data,Title2] = xlsread('conspreadsheet.xlsx', 4, 'B1:H8088'); % Extract all your number data and title 
Data = [First_column_data,num2cell(Data)]; % Concatenate your data
Title = [Title(1),Title2];                 % Concatenate your title
clear First_column Title2 First_column_data;
A= Data(:,1) %choose column
B = Data(:,4); %Choose column
C = Data(:,5); %Choose column
D = Data(:,6); %Choose column
E = Data(:,7); %Choose column
F = Data(:,8); %Choose column
G = Data(:,1); %Choose column
H = Data(:,2); %Choose column

   Rule1 = A(B==1 & F==1 | B==1 & F==1 & C==0 | B==1 & C==0 & D==0 & E==0); %select values whose rows fulfill the conditions listed

[User_in_Gim]

You're welcome !

I'm sorry but I ddon't understand what you want to do. Can you give me an example or provide more information ?

[Alexandra Brian]

I would like to select the string in the first column when the corresponding row's column D (fourth column) equals 1 and the column H (eigth column) equals 1 or the column D equals 1 and the column E equals 0 and the column H equals 1 or column H equals 1 and column E equals 0 and column G equals 0 and column H equals 0. So one example of this would be A2 in the conspreadsheet's example. I would like to return the strings in the first column when they fullfill these conditions.

[User_in_Gim]

Hi,

For me, your code line should works :

Rule1 = A(B==1 & F==1 | B==1 & F==1 & C==0 | B==1 & C==0 & D==0 & E==0); %select values whose rows fulfill the conditions listed

Your conditions were not clear (D==1 AND H==1) OR (D==1 AND E==0 AND H==1) (the second condition does nothing more than the first one), so I made another example :

If I want to extract all data from column A which depending on following conditions (each column name are the ones on the excel sheet) :

    D ==1 && H ==1
    or D==1 && E==0 && H ==1
    or H == 1 && E==0 && G ==0 && H ==0.

New_A = A((D==1 & H==1) | (F==0 & E==1)| (H==0 &G==0 & F==1));

And you get a 2150 *1 vector. D ==1 && H ==1 : 1000 hits.

D==1 && E==0 && H ==1 : 270 hits - (15 hits from the first line D=1 and H = 1)

H ==0 && G ==0 && F ==1 : 895 hits

So 1000 +255+895 = 2150.

[Alexandra Brian]

Yes, exactly what I meant. However, I'm trying to return the strings from column A.

[User_in_Gim]

The strings ?

With New_A = A(expression) you 'll get all data that agree the expression. I obtain a 2150*1 column vector with information from A

[Alexandra Brian]

When I have the following code:

clear all;
clc;
[~,First_column] = xlsread('conspreadsheet.xlsx',1,'A1:A8088');
Title(1)= First_column(1);  % Get the first row 
First_column_data = First_column(2:length(First_column)); % Get the data from the first column
[Data,Title2] = xlsread('conspreadsheet.xlsx', 1, 'B1:H8088'); % Extract all your number data and title 
Data = [First_column_data,num2cell(Data)]; % Concatenate the data
Title = [Title(1),Title2];                 % Concatenate the title
clear First_column Title2 First_column_data;

   A = Data(:,1); %choose column
   B = Data(:,2); %Choose column
   C = Data(:,3); %Choose column
   D = Data(:,4); %Choose column
   E = Data(:,5); %Choose column
   F = Data(:,6); %Choose column
   G = Data(:,7); %Choose column

ARule = A((B==1 & F==1) | (B==1 & F==1 & C==0) | (B==1 & F==0 & D==0 & E==0)); %select values whose rows fulfill the conditions listed

I receive the error: * Undefined operator '==' for input arguments of type 'cell'.

Error in concatenate (line 21) SymbolsRule = A((B==1 & F==1) | (B==1 & F==1 & C==0) | (B==1 & F==0 & D==0 & E==0)); %select values whose rows fulfill the conditions listed *

Are you adding this line of code to the concatenated code?

[User_in_Gim]

Yes, because your data type is cell. You should make your logical indexing with array.

So converting your data with cell2mat will clear the error. But don't convert your first column (A).

A= Data(:,1); %choose column

D = cell2mat(Data(:,4)); %Choose column
E = cell2mat(Data(:,5)); %Choose column
F = cell2mat(Data(:,6)); %Choose column
G = cell2mat(Data(:,7)); %Choose column
H = cell2mat(Data(:,8)); %Choose column

mat= ((D==0)&(E==1)&(F==0)&(G==1)&(H==0));

Be carefull with your data type. You can change the code according to your needs. For example, you can skip the following line Data = [First_column_data,num2cell(Data)] and just keep the first Data array. Doing this, you don't need to convert with cell2mat anymore.

But I choose to wrote Data = [First_column_data,num2cell(Data)], beacause i think it's more convenient. ( --> one variable with all your titles and another one with all your data).