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 )

Page 2 - Required data in vertical (date wise )

Thanks in Advance

CodePudding user response：

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)

Then press Enter

This might need some formatting depending on the merged cells.

Please see if this works

CodePudding user response：

Maybe this will work for you;

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!!