Hi, I was hoping to get some help. I have a table that currently looks like this: Table 1. Suburb #2006 A2011 A2016 A2006 B2011 B2016 B123411239171456811710110 I am trying to get the table down to: Table 2. Suburb #VariableYearValue123A20064123A201111123A201623123B20069 I understand I can transpose Table 1 where all the headers become rows and suburb # is repeated but how do I tidy up the variable names and create a new column 'Year' in the process? Thanks heaps in advance!

Split and transpose fields based on similar variable

Hi,

I was hoping to get some help. I have a table that currently looks like this:

Table 1.

Suburb #	2006 A	2011 A	2016 A	2006 B	2011 B	2016 B
123	4	11	23	9	17	1
456	8	11	7	10	11	0

I am trying to get the table down to:

Table 2.

Suburb #	Variable	Year	Value
123	A	2006	4
123	A	2011	11
123	A	2016	23
123	B	2006	9

I understand I can transpose Table 1 where all the headers become rows and suburb # is repeated but how do I tidy up the variable names and create a new column 'Year' in the process?

Thanks heaps in advance!

Connectors

Common Use Cases

Accepted answers

fmvizcaino

Hi @natyeo ,

Attached is my solution. I was able to separate the year from anything else using 2 regex tools.

One thing you need to still adapt is to standardize the variable column if needed and that you can do similarly to what I've done with find/replace tool to find the years.