Splitting data into two columns

[rajatsaxena2105]

Hi all,

I've my initial data like this

Application - Application Programming Language

1 = C# 2 = Java 3 = JavaScript 4 = VisualBasic .NET 5 = PHP 6 = C++ 7 = COBOL 8 = PL/SQL 9 = Perl 10 = Ruby 11 = Python 12 = ColdFusion 13 = Other 0 = Don't Know

I want to transform it into the following format

Application - Application Programming Language	Application - Application Programming Language
1	C#
2	Java
3	JavaScript
4	VisualBasic .NET
5	PHP
6	C++
7	COBOL
8	PL/SQL
9	Perl
10	Ruby
11	Python
12	ColdFusion
13	Other
0	Don't Know

Note: The initial dataset has only one record for all the values; the output data is all in different rows. I know this would be done using the transform, text-to-columns and the cross-tab tool but have not been able to do it.

Thanks,

Rajat

[patrick_digan]

@rajatsaxena2105 Let me know if the attached is what you're looking for. I used a text to columns tool and a regex tool to mix it up. 

[rajatsaxena2105]

@patrick_digan

Hi Patrick,

This is what i want but how do it do this for 10-20 columns at one go ? the data is like what's in the attached image.

Thanks,

Rajat

[patrick_digan · Accepted solution]

@rajatsaxena2105 See if the attached gets you pointed in the right direction. I've used a transpose at the beginning and then crosstab the data back at the end. The columns aren't quite in the right order at the end.

[rajatsaxena2105]

works perfectly, thanks a lot Patrick :)