Transpose Data based on row records

earthworm

earthworm

Well-known Member
Joined
May 19, 2009
Messages
773
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Have data like below

[TABLE="width: 865"]
<colgroup><col span="5"><col span="5"></colgroup><tbody>[TR]
[TD]Information 1[/TD]
[TD]Information 2[/TD]
[TD]Information 3[/TD]
[TD]Information 4[/TD]
[TD]Information 5[/TD]
[TD]Reference 1[/TD]
[TD]Reference 2[/TD]
[TD]Reference 3[/TD]
[TD]Reference 4[/TD]
[TD]Reference 5[/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info2[/TD]
[TD]Info10[/TD]
[TD]Info18[/TD]
[TD]Info26[/TD]
[TD]Info34[/TD]
[TD]B1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info5[/TD]
[TD]Info13[/TD]
[TD]Info21[/TD]
[TD]Info29[/TD]
[TD]Info37[/TD]
[TD]E1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F1[/TD]
[TD]F2[/TD]
[TD]F3[/TD]
[TD]F4[/TD]
[TD]F5[/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G1[/TD]
[TD]G2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H1[/TD]
[TD]H2[/TD]
[TD]H3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I want the data to appear like this

[TABLE="width: 541"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]Information 1[/TD]
[TD]Information 2[/TD]
[TD]Information 3[/TD]
[TD]Information 4[/TD]
[TD]Information 5[/TD]
[TD]Reference[/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD]Info2[/TD]
[TD]Info10[/TD]
[TD]Info18[/TD]
[TD]Info26[/TD]
[TD]Info34[/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD]Info5[/TD]
[TD]Info13[/TD]
[TD]Info21[/TD]
[TD]Info29[/TD]
[TD]Info37[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F2[/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F3[/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F4[/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F5[/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G1[/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G2[/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H1[/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H2[/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H3[/TD]
[/TR]
</tbody>[/TABLE]

I tired using the old pivot technique but it involves at-least 4-5 steps. is there any formula method ?

I want the record to duplicate based on the number of reference against each .
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
[TABLE="width: 1031"]
<colgroup><col><col><col span="2"><col><col><col span="8"></colgroup><tbody>[TR]
[TD]Information 1[/TD]
[TD]Information 2[/TD]
[TD]Information 3[/TD]
[TD]Information 4[/TD]
[TD]Information 5[/TD]
[TD]Reference 1[/TD]
[TD]Reference 2[/TD]
[TD]Reference 3[/TD]
[TD]Reference 4[/TD]
[TD="colspan: 2"]Reference 5[/TD]
[TD]helper[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info2[/TD]
[TD]Info10[/TD]
[TD]Info18[/TD]
[TD]Info26[/TD]
[TD]Info34[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D3[/TD]
[TD]D4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info5[/TD]
[TD]Info13[/TD]
[TD]Info21[/TD]
[TD]Info29[/TD]
[TD]Info37[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F1[/TD]
[TD]F2[/TD]
[TD]F3[/TD]
[TD]F4[/TD]
[TD]F5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G1[/TD]
[TD]G2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H1[/TD]
[TD]H2[/TD]
[TD]H3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]I want the data to appear like this[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Information 1[/TD]
[TD]Information 2[/TD]
[TD]Information 3[/TD]
[TD]Information 4[/TD]
[TD]Information 5[/TD]
[TD]Reference[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info2[/TD]
[TD]Info10[/TD]
[TD]Info18[/TD]
[TD]Info26[/TD]
[TD]Info34[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]this macro produces the lower table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Sub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info1[/TD]
[TD]Info9[/TD]
[TD]Info17[/TD]
[TD]Info25[/TD]
[TD]Info33[/TD]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]' Macro recorded 25/10/2017 by bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info2[/TD]
[TD]Info10[/TD]
[TD]Info18[/TD]
[TD]Info26[/TD]
[TD]Info34[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info3[/TD]
[TD]Info11[/TD]
[TD]Info19[/TD]
[TD]Info27[/TD]
[TD]Info35[/TD]
[TD]C3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] rrow = 25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For j = 2 To 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]20 For z = 1 To Cells(j, 12)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 1) = Cells(j, 1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info4[/TD]
[TD]Info12[/TD]
[TD]Info20[/TD]
[TD]Info28[/TD]
[TD]Info36[/TD]
[TD]D4[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 2) = Cells(j, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info5[/TD]
[TD]Info13[/TD]
[TD]Info21[/TD]
[TD]Info29[/TD]
[TD]Info37[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 3) = Cells(j, 3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F1[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 4) = Cells(j, 4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 5) = Cells(j, 5)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F3[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Cells(rrow, 6) = Cells(j, 5 + z)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F4[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] rrow = rrow + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info6[/TD]
[TD]Info14[/TD]
[TD]Info22[/TD]
[TD]Info30[/TD]
[TD]Info38[/TD]
[TD]F5[/TD]
[TD][/TD]
[TD][/TD]
[TD] Next z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G1[/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info7[/TD]
[TD]Info15[/TD]
[TD]Info23[/TD]
[TD]Info31[/TD]
[TD]Info39[/TD]
[TD]G2[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Info8[/TD]
[TD]Info16[/TD]
[TD]Info24[/TD]
[TD]Info32[/TD]
[TD]Info40[/TD]
[TD]H3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this for results on sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG25Oct29
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nRay(1 To 6, 1 To 1)
    nRay(6, 1) = "Reference"
    [COLOR="Navy"]For[/COLOR] n = 1 To 5
         nRay(n, 1) = Ray(1, n)
    [COLOR="Navy"]Next[/COLOR] n
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 6 To UBound(Ray, 2)
        [COLOR="Navy"]If[/COLOR] Ray(n, Ac) <> "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve nRay(1 To 6, 1 To c)
            [COLOR="Navy"]For[/COLOR] nn = 1 To 5
                nRay(nn, c) = Ray(n, nn)
            [COLOR="Navy"]Next[/COLOR] nn
            nRay(6, c) = Ray(n, Ac)
       [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
        .Value = Application.Transpose(nRay)
        .Borders.Weight = 2
        .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks . its working perfect.
 
Last edited:
Upvote 0
MickG said:
Try this for results on sheet2.
Code: 
[COLOR=Navy]Sub[/COLOR] MG25Oct29
[COLOR=Navy]Dim[/COLOR] Ray [COLOR=Navy]As[/COLOR] Variant, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] nn [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nRay(1 To 6, 1 To 1)
    nRay(6, 1) = "Reference"
    [COLOR=Navy]For[/COLOR] n = 1 To 5
         nRay(n, 1) = Ray(1, n)
    [COLOR=Navy]Next[/COLOR] n
c = 1
[COLOR=Navy]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR=Navy]For[/COLOR] Ac = 6 To UBound(Ray, 2)
        [COLOR=Navy]If[/COLOR] Ray(n, Ac) <> "" [COLOR=Navy]Then[/COLOR]
            c = c + 1
            ReDim Preserve nRay(1 To 6, 1 To c)
            [COLOR=Navy]For[/COLOR] nn = 1 To 5
                nRay(nn, c) = Ray(n, nn)
            [COLOR=Navy]Next[/COLOR] nn
            nRay(6, c) = Ray(n, Ac)
       [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
        .Value = Application.Transpose(nRay)
        .Borders.Weight = 2
        .Columns.AutoFit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thanks for the code . Now suppose i need to adjust the code as per my own dimension i.e more information column before reference which part of code shall i play with
 
Last edited:
Upvote 0
Alter columns as appropriate to your new data where shown in Code Comments.

Code: 
[COLOR=navy]Sub[/COLOR] MG25Oct37
[COLOR=navy]Dim[/COLOR] Ray [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Ray = ActiveSheet.Range("A1").CurrentRegion
ReDim nRay(1 To 6, 1 To 1) '[COLOR=green][B]>>6 is the last column in Array nRay.(Altert That)[/B][/COLOR]
    nRay(6, 1) = "Reference" '[COLOR=green][B]>>6 is the last column in Array nRay.(Altert That)[/B][/COLOR]
    For n = 1 To 5 '[COLOR=green][B]>> 5 is the last column before "Reference columns starts .(Alter that)[/B][/COLOR]
         nRay(n, 1) = Ray(1, n)
    [COLOR=navy]Next[/COLOR] n
c = 1
[COLOR=navy]For[/COLOR] n = 2 To UBound(Ray, 1)
    For Ac = 6 To UBound(Ray, 2) '[COLOR=green][B]> 6 is the starting column for reference,"Alter that).[/B][/COLOR]
        [COLOR=navy]If[/COLOR] Ray(n, Ac) <> "" [COLOR=navy]Then[/COLOR]
            c = c + 1
            ReDim Preserve nRay(1 To 6, 1 To c) '[COLOR=green][B]> 6 is the number of columns in the "nRay" array. (Alter that)[/B][/COLOR]
                nRay(nn, c) = Ray(n, nn)
            [COLOR=navy]Next[/COLOR] nn
            nRay(6, c) = Ray(n, Ac) '[COLOR=green][B]6 is the last columns in "nRay" array.(Alter that)[/B][/COLOR]
       [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] n
With Sheets("Sheet2").Range("A1").Resize(c, 6) '[COLOR=green][B]>6 is th final column in "nRay" array (Alter that)[/B][/COLOR]
        .Value = Application.Transpose(nRay)
        .Borders.Weight = 2
        .Columns.AutoFit
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thanks for the clarity. You rock. Please also suggest is there also any formula technique
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy the missing cells from another workbook
Replies
4
Views
491
Max1616
M
C
  • Question Question
Match and retrieve values from another workbook
Replies
3
Views
522
Cortex1000
C
N
  • Question Question
Email hyperlink: reference cells in another table on specific row for each email.
Replies
0
Views
433
NehaDu
N

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top