excel data into a single columns using loop

Does anyone know how to make this data into a single columns with spacing interval using for loop? from top data to bottom data

.

[Stephen23]

Original question:

https://www.mathworks.com/matlabcentral/answers/420493-extracting-data-from-excel-and-rewrite-it-into-specific-cell-in-a-loop

[LIM JIAXIN]

Hi stephen, the answer provided by the link doesnt help. maybe you can help me?

Answer 1

[ANKUR KUMAR]

What do you mean by equal spacing between each column. You cannot keep blank any element in matrix. You can do in cell. Here is an example.

A=xlsread('example.xlsx')
reshape(A,[],1) %if you want without keeping blank between each column
B=arrayfun(@(x) num2str(x) , A,'uni',0);
C=[B';{'','','';'','',''}];
reshape(C,[],1)

[LIM JIAXIN]

Is it possible to have blank space between them? and why is the result different from the command window compare to when i write it into the excel sheet?

[ANKUR KUMAR]

yes, its possible. Just put them in cell. Output of

 reshape(C,[],1)

is

 ans =

15×1 cell array

    '19'
    '18'
    '17'
    ''
    ''
    '19'
    '18'
    '17'
    ''
    ''
    '19'
    '18'
    '17'
    ''
    ''

The same is shown in excel too after writing in excel. xlswrite('example.xlsx',reshape(C,[],1),'sheet2','A1')

The output of above command is [see the attached image]

[LIM JIAXIN]

It's working! thank you but how do you make the value be 19 19 19, 18 18 18, 17 17 17 instead of 19 18 17

[ANKUR KUMAR]

Just remove transpose in this line

C=[B;{'','','';'','',''}];

Previously, there was B' , but if you want in this fashion [19 19 19, 18 18 18, 17 17 17], then remove transpose.

[LIM JIAXIN]

Thank you!

Final question, let says if there is a row of value [10, 20, 30] at range A1:C1 is added to make the output become [10 19 19 19, 20 18 18 18,30 17 17 17]. however the value of [19 19 19,18 18 18,17 17 17] starts at A3:C5 assuming there is random values in A2:C2. How do i write the code that is able to add the value from A1:C1 to produce the output?

[ANKUR KUMAR]

You can pass cell number as the argument, where you wish to start.

xlswrite('file.xlsx',C,'sheet1','A1')

[LIM JIAXIN]

Maybe you got my intention wrongly. What I meant was that for the previous example, now there is a additional 2 rows of value. So instead of having all the values coming out as the output, I wish to omit row 2. So in turn by combining the values of row 1 and row 3 onwards to give me a output of [10 19 19 19, 20 18 18 18,30 17 17 17] where the value of [10 20 30] comes from row 1 and [19 19 19,18 18 18,17 17 17] comes from row 3 onwards.

[ANKUR KUMAR]

Firstly make a matrix or cell in matlab itself what you wish to see in excel file.

>> A=xlsread('example.xlsx')

A =

      19    18    17
      19    18    17
      19    18    17

>> AA=[[10 20 30];A]

AA =

      10    20    30
      19    18    17
      19    18    17
      19    18    17

[LIM JIAXIN]

That doesn’t work as the values are all from the excel files. Is there a way to do it without manually typing in the value like AA=[[10 20 30]; A]?

[ANKUR KUMAR]

Yes surely. MATLAB can do everything what one can think of. Anyways, please attach the excel file and clearly mention your desired output. We will surely help you.

[LIM JIAXIN]

 https://www.dropbox.com/s/ee9sfwk6z15ntkk/example.xlsx?dl=0 the excel file is inside the link as i reached the limit of uploading files right now. So my input is under sheet 1 and the desired output can be seen from sheet 2.

[ANKUR KUMAR]

A=xlsread('example.xlsx')
A(2,:)=[];
reshape(A,[],1) %if you want without keeping blank between each column
B=arrayfun(@(x) num2str(x) , A,'uni',0)
C=[B;{'','','';'','',''}];
reshape(C,[],1)