Jul 28 2020 11:13 AM
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
Jul 28 2020 12:06 PM
Power Query works - name the range, query it, select first column, Unpivot other columns, remove Attribute column, return result back to Excel sheet.
Jul 28 2020 12:14 PM
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.
Jul 28 2020 01:10 PM
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
Loop
End Sub
Jul 28 2020 01:26 PM
Jul 28 2020 11:47 PM
Jul 29 2020 05:13 AM
Thank you. I'll try this file later today.
Jul 29 2020 05:14 AM
Thank you. I'll take a look at this file later today.
Jul 29 2020 05:55 AM
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?
Jul 29 2020 07:34 AM
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.
Jul 29 2020 08:46 AM
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.
Jul 29 2020 09:22 AM
SolutionYou 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
Jul 29 2020 02:11 PM
@kcb410 , here's the unpivoted file using PQ...
Jul 31 2020 06:10 AM
Thanks! That's perfect.
Jul 31 2020 06:29 AM
Thank you for the help. I can see how this works now!
Jul 29 2020 09:22 AM
SolutionYou 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