Transposing Certain Range of Columns Data

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Hello Folks, i want to transpose the data (Data is a string) with header in a range of column. See the example below for better understanding. Top table is the source data and bottom table is expected output. I have data from column A to AJ. 1st row is header. Each row carries data applicable to the respective headers in the column A to AJ. All the cells(F:AJ) will not carry data. I want to copy available data of each row from column F to AJ along with the header and should be inserted verticaly in F column of same row. So the next row has to move down based on the number of inserted columns data. Only copy the column header with has data and to its corresponding row.Number of rows is dynamic. i have no clue how to do VBA. Please give me a solution. Thank you. Stay home stay safe.

ABCDEFAAACADAJ
NameReg numberSchool nameAdvisorEmailXXXXXYYYYYZZZZZAAAAABBBBB
Anto22222xx schooljohnsds@@@@@!!!!####
cath33333yy schooljojnfsfsfd@*****!!!!!$$$$$&&&&
ABCDEFG
namereg numberschool nameadvisoremail
anto22222xx schooljohnsds@XXXXX@@@@
ZZZZZ!!!!
BBBBB####
cath33333yy schooljohnfsfsfd@YYYYY*****
ZZZZZ!!!!!
AAAAA$$$$$
BBBBB&&&&
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
madhuchelliah,

Can you share which version of Excel you are using and would you be open to the suggestion of using Power Query?
It would be as simple as:
  1. Load range in PQ
  2. selecting the columns A-E
  3. right click and select Unpivot Other Columns
  4. Save and load to Excel
1588852068054.png


Possible with Excel 2010 or higher on Windows.
 
Upvote 0
Hello GraH, it is working good. Thanks for your efforts. I am running some batch of VBA. So for continuity of my process i prefer doing in VBA. But great solution, a new learning for me. Thank you.
 
Upvote 0
That's great, continue the discovery! Sorry, can't be of assistance for VBA.
Stay safe, madhuchelliah.
 
Upvote 0
How about
VBA Code:
Sub madhuchelliah()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) - 5), 1 To 7)
   
   For r = 2 To UBound(Ary)
      For c = 6 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            For nc = 1 To 5
               Nary(nr, nc) = Ary(r, nc)
            Next nc
            Nary(nr, 6) = Ary(1, c)
            Nary(nr, 7) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 7).Value = Nary
End Sub
 
Upvote 0
How about
VBA Code:
Sub madhuchelliah()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
  
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) - 5), 1 To 7)
  
   For r = 2 To UBound(Ary)
      For c = 6 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            nr = nr + 1
            For nc = 1 To 5
               Nary(nr, nc) = Ary(r, nc)
            Next nc
            Nary(nr, 6) = Ary(1, c)
            Nary(nr, 7) = Ary(r, c)
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 7).Value = Nary
End Sub
Hello Fluff, it is working great. But if data is empty from F to AJ, A to E is not displaying in the row. It should be there with empty cells in G and H column. Hope you understood. Thank you. Have a great day. Stay home Stay safe.
 
Upvote 0
How about
VBA Code:
Sub madhuchelliah()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long, nc As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * (UBound(Ary, 2) - 5), 1 To 7)
   
   For r = 2 To UBound(Ary)
      nr = nr + 1
      For nc = 1 To 5
         Nary(nr, nc) = Ary(r, nc)
      Next nc
      For c = 6 To UBound(Ary, 2)
         If Ary(r, c) <> "" Then
            If Nary(nr, 6) = "" Then
               Nary(nr, 6) = Ary(1, c)
               Nary(nr, 7) = Ary(r, c)
            Else
               nr = nr + 1
               For nc = 1 To 5
                  Nary(nr, nc) = Ary(r, nc)
               Next nc
               Nary(nr, 6) = Ary(1, c)
               Nary(nr, 7) = Ary(r, c)
            End If
         End If
      Next c
   Next r
   Sheets("Sheet2").Range("A2").Resize(nr, 7).Value = Nary
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,224,851
Messages
6,181,395
Members
453,034
Latest member
mikdadhussain

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