copying records to downward dynamically

[rehanemis]

Hi,

I have spread sheet and wants it to be in new order.

Existing sheet Records

Roll No	Name	Ph1	ph2	ph3	ph4	ph5	ph6
1	abc	0300	677	748
2	def	767	87
3	hij	88	88	88	88	88	88

<tbody>
</tbody>

New look i wants as

Roll No	Name	Ph
1	abc	0300
1	abc	677
1	abc	748
2	def	767
2	def	87
3	hij	88
3	hij	88
3	hij	88
3	hij	88
3	hij	88
3	hij	88

<tbody>
</tbody>

Is there any way to dynamically adding the rows and records as shown in above example.

any suggestion?

 

[DRSteele]

PowerQuery will unpivot that data in a jiffy. Formula solutions will be more difficult and would require the new functions available in Excel365 Insider.