Match row and column in a number of excel file and return the value in the cell

Hi!

I am trying to process some data located in separate excel files (a few hundred). The files consist raw data directly downloaded from a data base in the form of books/files with export and import data during a period of time.

The actual location in the sheet varies between the documents, meaning it needs to be a solution that searches and find the value in the sheet. Ideally I would like to construct a number of loops that:

1. Finds the correcponding value that matches a row and a column. F.e the match between the row with name "China" and the column "Exported value in Q4 2015" and return the value (or the cell index).
2. Locate the value or cell index in question and return the value of it, and the correcponding 3 values on the row in question. If the value is located in C4, I would like to find the total value for C4+ D4+E4+ F4.
3. The end goal is a loop that search through all files and repeats the procedure above.

Answer 1

[Aditya]

Hi Amelia,

To achieve this task in MATLAB, you can use the "readtable" function to read Excel files and then process them to find the desired value. Here is a straightforward approach that should help you get started:

% Define the directory containing the Excel files
folderPath = 'path_to_your_excel_files';

% Get a list of all Excel files in the directory
fileList = dir(fullfile(folderPath, '*.xlsx'));

% Define the row and column you are looking for
targetRowName = 'China';
targetColumnName = 'Exported value in Q4 2015';

% Initialize a container to store results
results = [];

% Loop through each file
for i = 1:length(fileList)
    % Construct the full file path
    filePath = fullfile(folderPath, fileList(i).name);
    
    % Read the Excel file into a table
    tbl = readtable(filePath);
    
    % Find the column index for the target column
    colIndex = find(strcmp(tbl.Properties.VariableNames, targetColumnName));
    
    % Find the row index for the target row
    rowIndex = find(strcmp(tbl{:, 1}, targetRowName));
    
    % Check if both row and column were found
    if ~isempty(rowIndex) && ~isempty(colIndex)
        % Get the value at the intersection
        value = tbl{rowIndex, colIndex};
        
        % Calculate the sum of the row values
        rowValues = tbl{rowIndex, colIndex:colIndex+3};
        totalValue = sum(rowValues);
        
        % Store the result
        results = [results; {fileList(i).name, value, totalValue}];
    end
end

% Display the results
disp('Results:');
disp('File Name | Matched Value | Total Value');
disp(results);

Make sure to update 'path_to_your_excel_files' with the actual path where your Excel files are located. This script assumes that the first column of the Excel sheet contains the row names and that the column headers are in the first row. You may need to adjust the code if your data structure differs.

I hope this helps!