Question 407225 in Html Question posted in
Some basic documentation can be found here.

Html – How can I use PowerQuery (or basic Excel) to automatically transpose a single column of data into a table?

I have tried Googling and multiple different searches in StackOverflow, but I haven’t found a solution to this problem. I have a website (that requires SSO and login – so no direct queries allowed), that provides period data sets in tabular form. Whenever I copy from the site and paste into Excel (and I’ve tried every way possible), it always comes out as a single column of data:

My Data
Header A
Header B
Header C
Data A1
Data B1
Data C1
Data A2
Data B2
Data C2

How do I create a query that can automate a transposing of all of this data into the correct/original tabular format?

Header A Header B Header C
Data A1 Data B1 Data C1
Data A2 Data B2 Data C2
Data A3 Data B3 Data C3

I have tried solving this on several phases of this process:
Copy (the way I copy it)
Paste (every way that I can find in Excel to control the pasting of this)
Standard Excel tools
PowerQuery (but I’m limited in my PQ skills)

Tags:

2

Answers


  1. 0

    If you need to rearrange data in place, use the next macro:

    Sub Columnist()
  Dim cc As Long, cr As Long, lc As Long, lr As Long
  Dim i As Integer, j As Integer, k As Integer
  cc = ActiveCell.Column: lc = ActiveCell.End(xlToRight).Column
  cr = ActiveCell.Row: lr = ActiveCell.End(xlDown).Row
  j = cc: k = cr
  For i = cr To lr
    Cells(k, j) = Cells(i, cc)
    j = j + 1
    If j > lc Then
      j = cc: k = k + 1
    End If
  Next
  If j > cc Then k = k + 1
  Cells(k, cc).Resize(lr - k + 1, 1).Clear
End Sub

    You need to select the top left cell and copy it to right to have as much columns as necessary:

    enter image description here

    Then run the macro and get the result:

    enter image description here

    Login or Signup to reply.
  2. 0

    Use WRAPROWS:

    =WRAPROWS(A2:A10,3)

    You can change the 3 to the number of columns desired.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search