How to pull specific data from multiple excel sheets into a single table

Hello!

I'm trying to combine data from multiple excel sheets into a single table, and I'm looking to automate this as it seems to be growing every week. There is one specific census sheet, and about 4 or 5 other sheets that references the names in the sheet to particulary attributes. There is also a sheet that lists individual changes. I've been getting by using excel and v lookup but the logic is starting to get very confusing and I'm looking for a way to make the sheets easier. Additionally, sometimes the data is not the best but I need to filter through for additional changes.

Does anyone have any advice on

-pulling specific columns from the data sheets

-referencing those columns across multiple sheets to create a master sheet with the data I need

-adding filters so that the viewer can then select for certain attributes (name, location, amount of changes needed etc)

Any help either in the form of straight answers or resources would be appreciated!

Best,

Al

Answer 1

[Bob Thompson]

Reading excel files in with MATLAB can be done in a couple of different ways. First is xlsread, which might be the most applicable to what you're looking for. It will let you specify the range of cells you want, and which sheet they come from.

If you're reading a lot of excel files, or a lot of sheets from a file, then I would propose the use of the ActXServer. It's a much more complex setup, but a lot of the coding is similar to VBA (if you have any experience with that), and it runs much more quickly than xlsread/xlswrite when working in a lot of different excel files or sheets.

It is also possible to create a macro with VBA in excel directly, though I'm not enough of an expert to really begin to suggest how you can actually do that.

For the filters, both excel and MATLAB should be able to receive a user input and perform some logic to do the filtering you have in mind. Specific suggestions on that front would require some more knowledge about how the data is structured, and how you want to filter it.