Change lists into tables and tables into lists

Designed a userform that converts dimension formats, i.e. information from tabular format to list (or flat) format, and viceversa. Some options include preserving formatting or not, as well as including blank cells or not.

>>Form is here:Dim changer userform

>>Excel file with embedded form and a macro to make the form pop up is here:Dim_changer

>>Tutorial is here: https://www.youtube.com/watch?v=EvaRfIZo0QY

Code to inspect is here:

Option Explicit
Private Sub CommandButton1_Click()
'Dimension Fixer is by Amit Kohli (www.AmitKohli.com). You can use this macro free of charge, but please leave a comment if it's useful, and of course,
'this macro comes with no guarantees whatsoever. If you use this and something bad happens, you can't hold me liable.
'ok

'-------------DIMs
Dim rrange1, rrange2, datastarts, X, Y1 As Range
Dim i, i_ctr As Integer
Dim r, c As Variant
Dim cmt As Comment
Dim fixxed_cmt As String
Dim arr(99999, 5)

'-------------ERRORS
If Me.OB_Table_to_List.Value = False And Me.OB_List_to_table.Value = False Then
MsgBox ("Please select what I should do with your data")
Exit Sub
End If

'-------------PICK DIMENSIONS
Me.Hide
On Error Resume Next
Application.DisplayAlerts = False
If Me.OB_List_to_table Then 'Dim 1, ROW headings
Set rrange1 = Application.InputBox(Prompt:="Please select the Dimension that will become ROW HEADINGS", Title:="SPECIFY DIM 1", Type:=8)
Else
Set rrange1 = Application.InputBox(Prompt:="Please select the ROW HEADINGS", Title:="SPECIFY DIM 1", Type:=8)
End If

If rrange1 Is Nothing Then Exit Sub

If Me.OB_List_to_table Then 'Dim 2, COLUMN headings
Set rrange2 = Application.InputBox(Prompt:="Please select the Dimension that will become COLUMN HEADINGS", Title:="SPECIFY DIM 2", Type:=8)
Else
Set rrange2 = Application.InputBox(Prompt:="Please select the COLUMN HEADINGS", Title:="SPECIFY DIM 2", Type:=8)
End If

On Error GoTo 0
If rrange2 Is Nothing Then Exit Sub

Set datastarts = Application.InputBox(Prompt:="Please select first data-point.", Title:="SPECIFY DIM 2", Type:=8) 'First data point

Application.DisplayAlerts = True

If datastarts Is Nothing Then Exit Sub

If rrange1.Cells(1, 1).Column = datastarts.Column Then
Set X = rrange1
Set Y1 = rrange2
Else
Set X = rrange2
Set Y1 = rrange1
End If

If Me.CB_formatting Then
'In comments, replace line breaks with unique character ƒ, and " with '. (Just cleaning up for later)
For Each cmt In ActiveSheet.Comments
'fixxed_cmt = Replace(cmt.Text, Chr(10), "ƒ")
'fixxed_cmt = Replace(cmt.Text, Chr(13), "ƒ")
fixxed_cmt = Replace(cmt.Text, """", "'")
cmt.Delete
cmt.Parent.AddComment Text:=fixxed_cmt
Next
End If

'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=START! ARR 0=Row counter | 1=Column counter | 2=Value | 3=Cell Color | 4=Font Color | 5=Comment

i = 0

If Me.OB_Table_to_List Then '================================================================================== TABLE ------> LIST HERE
datastarts.Activate

For Each r In Y1
For Each c In X
Range("A1").Offset(r.Row - 1, c.Column - 1).Activate 'debug
arr(i, 0) = r
arr(i, 1) = c
arr(i, 2) = Range("A1").Offset(r.Row - 1, c.Column - 1).Formula
If Me.CB_formatting Then
arr(i, 3) = Range("A1").Offset(r.Row - 1, c.Column - 1).Interior.Color
arr(i, 4) = Range("A1").Offset(r.Row - 1, c.Column - 1).Font.Color
On Error Resume Next
arr(i, 5) = Range("A1").Offset(r.Row - 1, c.Column - 1).Comment.Text
On Error GoTo 0
End If

i = i + 1
Next
Next

'====OK, done, now spitting out results
Workbooks.Add
Range("B2").Activate

For i_ctr = 0 To i - 1
If Len(arr(i_ctr, 2)) <> 0 Or Me.CB_Blanks Then 'if cell isn't empty or if u want blanks
ActiveCell.Offset(0, 0).Value = arr(i_ctr, 0)
ActiveCell.Offset(0, 1).Value = arr(i_ctr, 1)
ActiveCell.Offset(0, 2).Value = arr(i_ctr, 2)
If Me.CB_formatting Then
ActiveCell.Offset(0, 2).Interior.Color = arr(i_ctr, 3)
ActiveCell.Offset(0, 2).Font.Color = arr(i_ctr, 4)
If Len(arr(i_ctr, 5)) <> 0 Then
ActiveCell.Offset(0, 2).NoteText arr(i_ctr, 5)
End If
End If
ActiveCell.Offset(1, 0).Activate
End If
Next

Else '========================================================================================================== LIST ------> TABLE HERE

For Each c In rrange1
datastarts.Offset(i, 0).Activate

arr(i, 0) = c.Value
arr(i, 1) = rrange2.Cells(i + 1, 1).Value
arr(i, 2) = datastarts.Offset(i, 0).Formula
If Me.CB_formatting Then
arr(i, 3) = datastarts.Offset(i, 0).Interior.Color
arr(i, 4) = datastarts.Offset(i, 0).Font.Color
On Error Resume Next
arr(i, 5) = datastarts.Offset(i, 0).Comment.Text
On Error GoTo 0
End If

i = i + 1
Next

'====OK, done, now spitting out results

Application.Workbooks.Add
For i_ctr = 0 To i - 1

If Len(arr(i_ctr, 2)) <> 0 Or Me.CB_Blanks Then 'if cell isn't empty or if u want blanks
Range("c1").Activate
'find correct column header
While ActiveCell.Offset(1 - ActiveCell.Row, 0).Value <> arr(i_ctr, 1) And ActiveCell.Offset(1 - ActiveCell.Row, 0).Value <> ""
ActiveCell.Offset(0, 1).Activate
Wend
'didn't find it.. labelling
If ActiveCell.Offset(1 - ActiveCell.Row, 0).Value = "" Then ActiveCell.Offset(1 - ActiveCell.Row, 0).Value = arr(i_ctr, 1)

ActiveCell.Offset(1, 0).Activate

'find correct row header
While ActiveCell.Offset(0, 1 - ActiveCell.Column).Value <> arr(i_ctr, 0) And ActiveCell.Offset(0, 1 - ActiveCell.Column).Value <> ""
ActiveCell.Offset(1, 0).Activate
Wend
'didn't find it.. labelling
If ActiveCell.Offset(0, 1 - ActiveCell.Column).Value = "" Then ActiveCell.Offset(0, 1 - ActiveCell.Column).Value = arr(i_ctr, 0)

ActiveCell.Formula = arr(i_ctr, 2) 'Found point! Putting data
If Me.CB_formatting Then
ActiveCell.Interior.Color = arr(i_ctr, 3)
ActiveCell.Font.Color = arr(i_ctr, 4)
If Len(arr(i_ctr, 5)) <> 0 Then
ActiveCell.NoteText arr(i_ctr, 5)
End If
End If
End If
Next

End If

End Sub

Private Sub CommandButton2_Click()
'Cancel
Unload Dim_changer
End Sub

  34 comments for “Change lists into tables and tables into lists

  1. Fernando
    2014/07/01 at 07:22

    Hi, I would like to use the macro, but when I plug it into the module, it doest not compile. I am not an expert so I don’t know what to do? please could you send me a file with the macro included.
    Thanks

    Fernando

    • Amit
      2014/07/01 at 11:28

      Done. Please see the description above, I’ve included a file with the form embedded and a macro that makes it pop up.

  2. fernando diaz
    2014/07/01 at 16:25

    Hi Amit
    The file works flawlessly, you are a genious
    Does the file have a limit regarding the number of files to be converted? if so, is it a way to increase it
    thanks again
    Fernando

    • Amit
      2014/07/02 at 08:02

      Number of files? You mean number of rows/columns? I don’t think I put a limit myself… maybe whatever limit Excel has? If you’re having a problem, email me your file.

  3. Stan Bowers
    2014/11/03 at 14:06

    Thanks for your Macro. That is an excellant Macro.

  4. Mary Wiley
    2015/02/19 at 05:00

    This macro has changed my life!

    At my previous job we had an analytical tool that “unpivoted” data that was provided to us in table format. I have been going crazy trying to find a way to do this, consistently and easily, in Excel.

    One assist, please. I would like to include more than one column in my row headings. I tried $A:$1:$C$150, but only got the data in column A in the output.

    THANK YOU.

    • Amit
      2015/02/19 at 17:52

      Glad this macro has changed your life! So now that your life belongs to me I want you to buy something red.

      In order to include more than one column in your row heading you should combine the columns prior to running the macro. An easy way would be to use excel’s concatenate function:

      =A1 & "|" & B1
      

      for example will make one column of both of those… then use this new column as your row headings. I included the “|” in the middle as a unique character so that you can seperate the columns back out again later if u need to. In case in your data you have “|” already then use something weird like “ƒ”. Whatever you use as a delimiter should be unique.

      I have another macro that I use to make this stitching operation quickly… I’ll eventually be releasing it as part of a larger data-processing toolkit… but if you really want the stitching macro now, I’ll give it to u.

  5. Nibs
    2015/02/19 at 12:08

    I love this. Exactly what I was looking for. But how can I get the macro to create the table in the same workbook and I select the cell to start the list from?
    Also what is Me in your macro above?

    • Amit
      2015/02/19 at 17:44

      Glad you like it 🙂

      Instead of:

      Workbooks.Add
      Range("B2").Activate
      

      Put:

      Range("A1").Activate
      

      where A1 is the range in your current file where it should start dumping out results. Be careful though, it might overwrite data. That’s why I always start fresh… but as you wish. 🙂

      • Mary Wiley
        2015/02/19 at 22:08

        Got it. Then I can just use text to columns in the output. Thanks

  6. Emma
    2015/09/17 at 08:41

    Hello,

    This looks amazing but I am unsure how to get the macro into my personal workbook. Would you be able to give me some guidance?

    Many thanks

    Emma

    • Amit
      2015/09/17 at 13:49

      Hrm. How come? Have you tried to open up the excel file and then dragged the userform to your personal?

  7. Maarten
    2015/10/19 at 10:04

    You did a wonderful job man! Just did a 180 x 50 matrix, it took him a while but it performed flawless!

    Thanks!

    • Amit
      2015/10/20 at 20:02

      Welcome 🙂

  8. Svemirko
    2016/05/19 at 10:43

    This looks great!
    But only one question: Would it be possible to use text as data?
    When I try this it gives me error.

    • Amit
      2016/05/20 at 11:01

      hrm… I feel like I have done it before. Have you tried enabling and disabling all options? Also, sometimes if your text is more than 254 characters it might cause a problem. If none of that works, send me your file and I’ll take a look.

      • Svemirko
        2016/05/20 at 11:08

        I tried to index the text with numbers so I could replace it later with a lookup but the macro went into a loop.

        If you like i will send you the file to try it out but I managed to do it with power query. Tnx for the help!

  9. 2016/06/15 at 01:50

    This macro is amazing…it was what I was looking for the last few months. Thanks!
    I have a problem though, after using it to convert a 12×31 weather data table into 1×365 list for few years of data it crashed on an excel 2016. What should I do? FYI I download the macro from this site and used it without any change.

    • Amit
      2016/06/19 at 21:23

      happy you like it! Were you trying to do all the years at the same time? Maybe you hit the bottom of the excel file? It would be helpful if you give me a screenshot of the error… we can start like that.

  10. Sofyan
    2016/09/07 at 08:04

    Hi, Great work there, Is there a possibility , That i can get code for only list to table. I am new to vba and spent few hours but couldn’t figure it out. what to keep and what to remove . Any help would be appreciated

    Thank You

    • Sofyan
      2016/09/07 at 08:11

      I mean , I have done some manipulation in my data and made a list out of it, now i need to make it a table again, so for end to end automation , i need to define the ranges and not take it at runtime.

      • Amit
        2016/09/09 at 03:22

        So in the beginning of the macro, i take the ranges.. You can see that i ask for them in the inputbox fields. Instead of asking for input, define the range there. Also, if you only need list to table, have you looked at pivot tables?

  11. victor
    2016/09/30 at 22:01

    Just wanted to say THANKS!!!

    • Amit
      2016/10/03 at 18:34

      🙂

  12. Jim Bob
    2016/10/12 at 19:05

    Hi there,
    I’m trying to use the macro on a list of 100k+ rows but it freezes at row ~10k. I changed the variables i and i_ctr to long and also the array to arr(1000000, 5) but it still doesn’t work. Any suggestion?

    thanks

    • Amit
      2016/10/14 at 17:20

      My suggestion? Upgrade and learn R. 😀 (Just kidding kind of). Um… are you going from List -> Table or other way around? When it freezes does it freeze and crash, or just freeze?

      • Jim Bob
        2016/10/18 at 12:05

        I’ve heard of R … for some reason we don’t/can’t have it here.
        I was finally able to get the thing working. Just had to be (very) patient. It took about an hour to complete the task. Not as fast as I hoped, but definitely faster than any other method we were considering!

  13. Brandon Aict
    2016/11/28 at 04:08

    Thanks! This is perfect 🙂

    Saved me a lot of time.

  14. Curious
    2017/03/14 at 17:04

    As someone said this macro saved my life. It’s amazing.
    But there is limit, if the range is too large it stops.

    For i_ctr = 0 To i – 1

    • Amit
      2017/03/17 at 09:36

      Glad your life has been saved 🙂 are you doing lists->tables or tables->lists? How big are you talking?

  15. Curious
    2017/03/17 at 15:57

    Hi, I am doing tables to list. Something over 40 columns and 500 rows it does not run anymore. So I keep splinting around this range. But I work with some tables over 200 columns and 1000 rows.

    • Amit
      2017/03/18 at 09:47

      What do you mean “it doesn’t run anymore”? If you’re talking about the screen going blank temporarily, just let it… I feel like 20000 rows shouldn’t be a challenge. But perhaps you know better. These days I do my transformations in R… much more efficient and the limit is in the millions of rows. Give it a try!

  16. Curious
    2017/06/06 at 11:44

    Thanks Amit, I will give it a try.

    About the macro it comes with “Run-time Error ‘6’, Overflow”

  17. 2017/06/06 at 13:25

    Yeah… for some reason it’s too much data… not sure why. Try R! Or split it up into smaller pieces! 🙂

Leave a Reply

%d bloggers like this: