Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am looking to see if I can solve this problem using Excel- if not, I can write a script that would help me do this instead but it would be a little more time consuming.

Here is my data set:

enter image description here

This is just a sample of my data. The column on the left is an identification ID and the column on the right are some properties tied to that ID.

I want to manipulate the data in such a way that for each unique element on the right column, I have one row and these rows would have the repeated identification numbers (from the left column). The way it would look is:

enter image description here

I know about the "Text to Columns" feature in Excel but what I am trying to do seems more like "Text to Rows".

Any input is appreciated!

share|improve this question
add comment

1 Answer

A bit of a manual process but you could do the following:

  1. Use Text to Columns to separate the properties.
  2. Copy the properties and use Paste Special and Transpose to flip the column data to rows.
  3. Fill the identification ID down to have it align with the properties.

If I had to fix up the sheet once that would probably be how I would do it. If I had to do it multiple times I would write a VB script to automate the process. You can see my answer to this other question about transposing rows/columns for an example of a script to do this.

share|improve this answer
    
It does work. The only thing is that I have to manually add empty rows to fill in the transposed data. Any suggestions? –  karancan Apr 9 '13 at 17:05
    
I would probably copy it to a new Workbook or new Worksheet having them open side-by-side to make the copy/paste easier. –  Brad Patton Apr 9 '13 at 17:48
1  
Thanks, I ended up doing it in PHP. Was a breeze. Anyhow, learnt a couple more thing about Excel through this. –  karancan Apr 14 '13 at 14:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.