Converting data from multiple choices per line, to 1 choice per line

[caffeine_demon]

Hi,

Is there an easy way in excel to convert a spreadsheet from 1 line with multiple "options", to one line per option - ie:
----------------
from:

number option 1 time option 2 time option 3 time
1 first 10am second 1pm
2 2first 11am,
3 3first 12pm 3second 2pm 3third 2:30pm
--------------
to:

Number option name time
1 1 first 10am
1 2 second 1pm
2 1 2first 11am,
3 1 3first 12pm
3 2 3second 2pm
3 3 3third 2:30pm
-------------

and how about going the other way? (note - we may have more than 2 columns per data item)

Only way I can think of is a macro.

 

[Sektor]

How much options do you have? Does amount change?

 

[caffeine_demon]

How much options do you have? Does amount change?

In my set, each person can have up to 6 options, and it varies

 

[Norie]

Here's something that worked with your small data sample, and should with more options.

Option Explicit
 
Sub Trans()
Dim rngSrc As Range
Dim rngDst As Range
Dim NoOpts As Long
Dim I As Long
 
    Set rngSrc = Worksheets("Sheet1").Range("A2")
    
    Set rngDst = Worksheets("Sheet2").Range("A2")
    
    While rngSrc.Value <> ""
        NoOpts = Cells(rngSrc.Row, Columns.Count).End(xlToLeft).Column
        
        For I = 2 To NoOpts Step 2
        
            rngDst.Value = rngSrc.Value
            
            rngDst.Offset(, 1) = Int((I - 1) / 2) + 1
            
            Worksheets("Sheet1").Cells(rngSrc.Row, I).Resize(, 2).Copy rngDst.Offset(, 2)
            
            Set rngDst = rngDst.Offset(1)
            
        Next I
        
    Set rngSrc = rngSrc.Offset(1)
    
    Wend

End Sub

 

[caffeine_demon]

TA!