SOLVED

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

Copper 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:

Before.png

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

 

After.png

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

14 Replies

@kcb410 

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.

@kcb410 

 

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

 

@kcb410 

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

image.png

@kcb410 

 

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.

 

 

@kcb410 

 

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 (Copper Contributor)
Solution

@kcb410 

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!

1 best response

Accepted Solutions
best response confirmed by kcb410 (Copper Contributor)
Solution

@kcb410 

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

View solution in original post