Help to create this

[Marco]

Hi. I need help to create this:

I've got my actual view like this: (Rows)
ID DInicio DFim
3000 38279 38340
3000 38353 38561
3000 38578 38927

and I need to make it look like this: (one reocord, columns)
ID DInicio DFim DInicio DFim DInicio DFim
3000 38279 38340 38353 38561 38578 38927


Please help me out here.

Regards in advance.
Marco

 

[Sean Timmons]

Presumably you have multiple ID's.

do they all have the same number of rows?

If you only have a limited number of rows per ID, but they don't all have
the same number of rows.. something like this...

In D4,

=if($A3=$A2,B3,"")

and D5 would be

=if($A3=$A2,B4,"")

Then, copy this If statement DOWN the max number of rows you'd need.

then, either use OFFSET() or cut and paste the values across your first row.

Once done, copy and paste these fornulas down your rows.

then...

to the far right of row 2, do.. =if(A2=A1,"X","")

paste all the way down..

Copy and paste special all rows as values.

put a filter on, and delete any row with an X in that last column to remove
dups.

 

[Don Guillett]

This should do it for you.

Sub lineemup()
Dim i, lc1, lc2 As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Cells.WrapText = False

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1

If Cells(i - 1, 1) = Cells(i, 1) Then
lc1 = Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1
lc2 = Cells(i, Columns.Count).End(xlToLeft).Column
Cells(i, 2).Resize(1, lc2).Copy Cells(i - 1, lc1)
Rows(i).Delete
End If

Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Marco]

It's simply great.

Are you a genius?


Thank you very much.
Marco

 

[Don Guillett]

Of course, everyone says so..... Well, at least my wife..

 

[Marco]

That's way we love our wifes so much. for the support they give us.

By thhe way, this code doesn't work in Excel 2007. The pc hangs.

It works perfect in 2003

Ciao.
Marco

 

[Don Guillett]

I just tested in xl2007 thinking that maybe it didn't work if in a sheet
module. Worked fine in both a regular module and from a sheet module. I
can't think of a reason why you had a problem but it is NOT the code.