How to process information on one excel spreadsheet and reformat the information onto another excel spreadsheet?

Hi, I have an excel spreadsheet called RawExcel.xlsx which has 13 columns and 37 rows but not all of the information is needed. I would like to get rid of alot of information and condense it down to 5 columns.

How would I write code to do the following process:

1. Delete and remove Columns C, E, G, J, K, L, and M
2. Delete and remove all rows containing single and double digits under the FIND NO (Column 1)
3. Delete and remove second row and rewrite top row to FIND NO (Column 1), QTY (Column 2), PART NUMBER (Column 3), REF NUMBER (Column 4), and REF NUMBER (Column 5).
4. Under the new REF DESIGNATOR column, concatinate strings contained on row 31 and 32 to form 'J32, K131, K132, J34, K133'
5. Delete and remove all empty cells
6. Write remaining information into another excel called ProcessedExcel.xlsx

The information in the ProcessedExcel.xlsx should look like this:

Any help will be greatly appreciated.

[John D'Errico]

Surely this is better done using macros, not working in MATLAB at all?

[Paul Badra]

Yes, macros could work for this portion..the thing is, there is more data i'm not showing that needs to be processed.

Answer 1

[SAI SRUJAN]

Hi paul,

I understand that you are trying to process information on one excel spreadsheet and reformat the information onto another excel spreadsheet.

To achieve the described tasks using MATLAB, you can follow the steps below. This script reads data from RawExcel.xlsx, processes it according to your specifications, and then writes the processed data to ProcessedExcel.xlsx.

For a comprehensive understanding of reading data from an Excel sheet in MATLAB and utilizing table methods effectively, please go through the following relevant documentation.

https://www.mathworks.com/help/matlab/import_export/read-spreadsheet-data-into-table.html

https://www.mathworks.com/help/matlab/ref/table.html

I hope this helps!

[Paul Badra]

Thank you, this should work.