Finding Row and Column No. For Particular Searches

Hi Mathworks

I have a large dataset, where some of my values are extreme and need to be double checked. Thus, if anyone can answer just one of the request I will really appreciate it!

1. Is it possible to search for exact values in a table and find the placement (the row and column)?
2. Is it possible to search in a table for the name of the variable, e.g. "DSV" as in the attached photo and find the placement (the row and column?
3. Is it possible to search in a table to find values that exceed a threshold and find the placement (the row and column)?

Regarding the first question I can instead of the table use an array and e.g. find(MKT_Array==100), which result in a long list of values where the first one is 1497. Is this an indication of the row and column placement?

I've attached a screen of the MV table I'd like to search in. I have an almost identical one as an array (I've just used table2array).

All the best,

Christoffer

Answer 1

[Vinai Datta Thatiparthi]

Hey Christoffer!

Yes, MATLAB can support all the queries that you posted. Firstly, use the function readtable to import the Excel sheet into MATLAB. This function creates a table that you can access into.

table = readtable('DataDK.xlsx'); % Imports table into MATLAB

To find the exact value in the table and get its placement -

[a,b] = find(table.ColumnName == value); 

If there are multiple hits, 'a' and 'b' will hold multiple values. If you want to do a global search for a value within the table, follow the same procedure, but use readmatrix to import the shhet instead.

To search for the position of the Column Name in the table -

[a,b] = find(strcmp(table.Properties.VariableNames,'ColumnName'));

To find values in the table that exceed a particular threshold -

[a,b] = find(table.ColumnName > ThresholdValue); 

"...which result in a long list of values where the first one is 1497..."

Yes, since MATLAB follows column-major layout, "1497" is the index of the value when counted column-wise. Since your Excel sheet has 349 rows, 1497 corresponds to the position (101,4).

Additionally, this Documentation link on Accessing Data in Tables may be useful to you.

Hope this helps!

[Christoffer Benneballe]

Thank you, Vinai!

That's exactly what I was searching for!