Here is a question I just answered: I have 600 cells all in one column. To the left of that I have 3 repeating entries to label each cell. What I would like to do is transform the one column into 3 columns. One column for each label while keeping them linked?

Introduction

I’ve got two solutions for you. Solution one works but it is not as smart as solution two. If you have never used Power Query before you are in for a treat, see solution two for that.

For both solutions, I set up two columns: one for the labels and one for the values. I converted this into an Excel table and it and solution one look like this:

SOLUTION ONE

  • Cell K2=A2
  • Cell L2=B2
  • Cell M2=A3
  • Cell N2=B3
  • Cell O2=A4
  • Cell P2=B4

Now, select the range of cells K2:P4 and fill down to the end of your input table. You should have exactly what I show here … columns K and L contain only aaa data … all linked to the input or source. Unfortunately gaps between the rows.

If you add new data to your Excel table, you need to fill down in your output range to include them there.

SOLUTION TWO

Look at the much better solution and then see how I did it:

For this solution I used Power Query/Get & Transform

Create the Excel table in columns A and B and select any cell in that table

Data … From Table/Range … this starts the Query Editor

Change the name of the query to aaa … or your own first label

For the Label column deselect bbb and ccc … this leaves only aaa data selected in the Label and the Value columns

Click Close and Load to … select current worksheet and cell D1 in my case

Duplicate your query and repeat the above but this time change the name of the query to bbb and select bbb for the Label column to leave only bbb data in that model

Click Close and Load to … select current worksheet and cell F1 in my case

Duplicate and do it again … name change to ccc and so on

Click Close and Load to … select current worksheet and cell H1 in my caseIf you change any of the values in the source/input table, just refresh the queries and they update

Since you have used an Excel table as your input, when you add more data to it and refresh the query, it updates AUTOMATICALLY to include whatever you did!

Download my Excel file here … copy_over

 

Duncan Williamson

13th June 2018

 

%d bloggers like this: