Forum Discussion
Convert 912 rows and 16 columns into 2 columns....with a twist
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!
Paul
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
14 Replies
Thank you. I'll take a look at this file later today.
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 IntegerY = 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
Loop
End SubI'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.
Power Query works - name the range, query it, select first column, Unpivot other columns, remove Attribute column, return result back to Excel sheet.
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.
