Convert 912 rows and 16 columns into 2 columns....with a twist

Occasional Contributor

Hello All,


I've got a spreadsheet with 912 rows and 16 columns, each is row contains a product model # and the 1 to 15 colours that are available for each model. I need to convert this to a vertical set up with 2 columns, Model# and Colours for each model. Here's it's current setup:


Here's an example what we need the final to look like:



As you can see some models have one colour and others might have 5, 10 or 15.


Can anyone point me in the right direction with a method to achieve this?


Any help would be appreciated. Thank you!



14 Replies


Power Query works - name the range, query it, select first column, Unpivot other columns, remove Attribute column, return result back to Excel sheet.

@Sergei Baklan 


Thanks for the reply.


I've tried using unpivot other but I've been unable to make it format the data the way I need it. For instance if there's 2 colours I need the model number repeated twice, if there's 7 colours I need the model number repeated 7 times. I know Power Query will achieve this, I just don't know how.



Have you used Macros ?


This code picks colors from right to each product.

It is important to select first product-code as activecell

before running this code.

Select first product-code from your list and run this code.

You can make this actions to a copy of your original data.


The macro picks up colorinformation from right to activecell ( 14 columns )

and adds a row for new color if needed. Macro stops when activecell value

is empty.


Sub ColumnsToRows()

Dim X As Integer
Dim Y As Integer

Y = 0
Do While ActiveCell.Value <> ""
     For X = 2 To 15
         If ActiveCell.Offset(0, X).Value <> "" Then
               ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
               ActiveCell.Offset(1, 0).Value = ActiveCell.Value
               ActiveCell.Offset(1, 1).Value = ActiveCell.Offset(0, X).Value
               Y = Y + 1
        End If
    Next X

   ActiveCell.Offset(Y + 1, 0).Select
   Y = 0


End Sub



Please check in attached file, that's just two steps query




Here is an excamble file with which you can test the idea.


@Sergei Baklan 

Thank you. I'll try this file later today.

@Olli Haavisto 

Thank you. I'll take a look at this file later today.

@Sergei Baklan 

Hi Sergei,


Thanks for sending that file.

Excuse my ignorance on this topic, I've never used Power Query. I assume the first step is Unpivot other columns. What's the final step/query?


@Olli Haavisto 

I'm testing the macro and it's repeating colours and shuffling the order for some reason.

Do you have any idea what might cause that? I've attached the files for reference.





Maybe you have changed selection when macro is running ? It is only way that I can think

could cause the behaviour you described. Run macro only once and do not interrupt it when

it runs. I made a video, it shows you how macro proceeds...

but video format is not allowed to send here.


You can run any macro step by step using F8 function key.

Function key F5 runs macro to the end if there is no stopping points in code.

I added one row as an comment to code. If you take comment-mark away and run macro

after that it works much faster without updating screen while working.


It is fine feature with macros that a person can make them work in so many ways. Implementation or

the way to archieve the goal depends on person who makes the code and his imagination.







Best Response confirmed by kcb410 (Occasional Contributor)


You may open the file, Data->Query and Connections, on the right pane double click on query (or Edit from right click menu), query editor will be opened, here you will see the steps

@kcb410 , here's the unpivoted file using PQ...

@Sergei Baklan 

Thank you for the help. I can see how this works now!