Google Docs / Excel how to transform redundant row in columns

Question

I have a list of redundant rows like this

See my Google sheet

Target       FRBBED05517    
Job state    EXECUTION_ORDERED  
Target       FRBBED05523    
Job state    EXECUTION_ERROR    
Target       FRBBED05547    
Job state    EXECUTION_ERROR    
Target       FRBBED05584    
Job state    EXECUTION_ERROR

And I want to transform them in columns like this
See my Google sheet

 FRBBED05517     EXECUTION_ORDERED
 FRBBED05523     EXECUTION_ERROR
 FRBBED05547     EXECUTION_ERROR
 FRBBED05584     EXECUTION_ERROR
 FRBBED05620     EXECUTION_ERROR

Is it a simple way to do it ?

I have here a example to test Google sheet

Answer 1

You need something like this

Fill column L with:

=INDEX($B$1:$B$16,(ROW()-ROW($L$2))*2+1)

And Column M with:

=INDEX($B$1:$B$16,(ROW()-ROW($L$2))*2+2)

$B$1:$B$16 is area of values to display (targets and job state)

$L$2 can be somewhere in first row of area filled with formulas above.

//You can use any other columns instad L and M

Answer 2

Do you need a formula or just a one go task?

If you don't need to do this often I think manual approach is the quickest here: copy your second column to the third column to the right position (shifting up one row) then sort by first column and you can quickly delete all rows now where first column is "job state".

If you need to do this frequently than even a simple macro can do this for you.