0

So I have this table in Excel as below:

An Excel table for sales.

The table has 14 columns and 1500 rows depicting car (for simplicity) sales.

Now, what I want is to have the table transformed like this:

Desired Excel table

I know there is such thing as a transpose formula (which I used to build the second table above), but I most obviously can't do this for every single one of all the 1500 rows in the table.

Can anyone help me out on the most efficient way to do this? VBA code or macros are welcome.

Thanks a lot!

Community
  • 1
  • 1
user71812
  • 457
  • 2
  • 4
  • 19
  • 1
    Show us your code so we can help you. – dadler Jan 26 '18 at 09:45
  • This might be a homework question. – Jem Eripol Jan 26 '18 at 09:45
  • @JemEripol -- Homework questions are allowed as long as an attempt is being made. *"Questions asking for homework help **must include a summary of the work you've done so far** to solve the problem, and a description of the difficulty you are having solving it."* ([Source](https://stackoverflow.com/help/on-topic)). Also: [How do I ask and answer homework questions?](https://meta.stackoverflow.com/a/334823/8112776) – ashleedawg Jan 26 '18 at 09:47
  • Create a for-loop that takes every row of the base table Inside that loop create loop that takes every column and write it in another sheet in transposed manner – helpME1986 Jan 26 '18 at 09:49
  • 1
    (However, personal *pet peeve*, here **&** IRL : when sentences start with ***"So…"***) – ashleedawg Jan 26 '18 at 09:51
  • Hi, thanks for everyone's comments, really appreciate it! But no, this is most definitely not a homework question as this is a modified set of data I used for my work. And I didn't use any code for this; I am currently manually copying-pasting them in transpose... that's why I am asking about it here. – user71812 Jan 26 '18 at 09:53
  • 1
    I had the same issue: https://stackoverflow.com/questions/48337054/how-to-create-a-pivot-table-when-fields-are-on-horizontal-and-vertical-axes – Pierre44 Jan 26 '18 at 09:56
  • @Pierre44, THANKS A LOT! I checked your post and the idea to use PowerQuery for this is brilliant! Thanks again! (How do I upvote your answer, though?) – user71812 Jan 26 '18 at 10:06
  • 2
    Do you need the data to be displayed *exactly* as in the example? If not - if you simply want to group branches and periods together, a simple pivot table would do the job. For example, branch IDs would be the columns (one ID per column); and the months would be the rows. The intersections would be the totals. No VBA required for this. – Chris Melville Jan 26 '18 at 10:17
  • @user71812 you can upvote my question (and the answer from jeffrey) in the other thread ;) – Pierre44 Jan 26 '18 at 10:48

1 Answers1

-1

Try something like this

 Sub UnPivotData()
 Dim wsSource As Worksheet, wsDest As Worksheet
 Dim x, y, i As Long, j As Long, n As Long

'Assuming data is on a sheet called "Sheet1", change it if required
Set wsSource = Sheets("Sheet1")

x = wsSource.Cells(1).CurrentRegion.Value
ReDim y(1 To UBound(x, 1) * UBound(x, 2), 1 To 2)

For i = 2 To UBound(x, 1)
    For j = 2 To UBound(x, 2)
        If x(i, j) <> "" Then
            n = n + 1
            y(n, 1) = x(i, 1)
            y(n, 2) = x(i, j)
        End If
    Next
Next

On Error Resume Next
Set wsDest = Sheets("Transposed")
wsDest.Cells.Clear
On Error GoTo 0

If wsDest Is Nothing Then
    Sheets.Add(after:=wsSource).Name = "Transposed"
    Set wsDest = ActiveSheet
End If
wsDest.Range("A1:B1").Value = Array("Number", "Deatils")
wsDest.Range("A1").Resize(UBound(y), 550).Value = y
wsDest.Range("A1").CurrentRegion.Borders.Color = vbBlack
MsgBox "Data Transposed Successfully.", vbInformation, "Done!"
End Sub
Sidney Bookeart
  • 1
  • 1
  • 1
  • 5