CODEWITHSUNDEEP
excelexcel-formula
Rahul W
Rahul W

Reputation: 193

How to copy data from horizontal to Vertical in Excel

I am not expert in excel and need one help for making the summary sheet of data.

I want data from one page to another page where in first page i have data in horizontal ( Columns ) dates wise which i need in vertical row (same as date wise )

Ref the attached image for more clarification.

Page 1 ( Data in Horizontal Date Wise ) enter image description here

Page 2 - Required data in vertical (date wise ) enter image description here

Thanks in Advance

Upvotes: 0

Views: 700

Answers (2)

Rohit Mehta
Rohit Mehta

Reputation: 11

You might try copying and then pasting as Transpose.

Use "Ctrl + A" for selecting the whole data. Then, "Ctrl+ C" for copying.

For pasting as transpose use- Alt + E + S + V + E (Press one by one- don't keep on holding the keys)

Alternatively, right click in the cell and select 'Transpose' from the options.

Then press Enter

This might need some formatting depending on the merged cells.

Please see if this works

Upvotes: 1

Bob
Bob

Reputation: 11

Maybe this will work for you;

enter image description here

I tried to recreate what seems the thing that you would need. In order to get there I did the following:

  • Copy the same date in all three columns (instead of a merged value). In Cell E2 for example the formula is "+D2". Once you have that you can copy and paste it in the rest of the needed cells.
  • The same I did for "Flat No". There are several ways to get the data there, but you could do the same as I indicated in the previous step.

Once the "structure" is complete, I wrote the following formula:

=IF($B11=$C$4;+SUMIFS($D$4:$R$4;$D$2:$R$2;$C11;$D$3:$R$3;D$10);IF($B11=$C$5;+SUMIFS($D$5:$R$5;$D$2:$R$2;$C11;$D$3:$R$3;D$10);IF($B11=$C$6;+SUMIFS($D$6:$R$6;$D$2:$R$2;$C11;$D$3:$R$3;D$10);IF($B11=$C$7;+SUMIFS($D$7:$R$7;$D$2:$R$2;$C11;$D$3:$R$3;D$10);"CHECK"))))

For visual references, please see the attached picture.

Basically it is an SUMIFS formula within an various IF formulas. So it returs the data in Cell D11 if both Flat No, Date and "A, B or C" is the same as indicated in cells "D10", "B11" and "C11".

Hope this helps!!

Upvotes: 1

Related Questions