rearranging data

[Utkarsh]

Hi
I have data in the following form, with each ID having numerous events
against it:
ID End Date Event
1 1/1/2004 A
1 1/2/2004 B
1 1/3/2004 C
2 1/4/2004 X
2 1/5/2004 Y
3 1/6/2004 M
3 1/7/2004 N

I need to pick up the last two events (by date) and organise them as
follows:

ID Second last date Second last event Last date Last event
1 1/2/2004 B 1/3/2004 C
2 1/4/2004 X 1/5/2004 Y
3 1/6/2004 M 1/7/2004 N


Any easy way of doing this?

Thanks, Utkarsh

 

[Guest]

Hi Utkarsh,

I would just write a subroutine with a couple of loops to work through the
data copying the last two items of each group to the new location.

'start at the beginning of the list
currentID = firstID
'loop until you've reached the end of the list
loop until currentID is blank
'start looking for the next set of ID's
nextID = currentID
'loop until you've found the first ID in the next set
loop until nextID <> currentID
increment nextID
end loop
'based on the location of the first ID in the next set,
'identify the second last and last ID's in this set
lastIDinGroup = ID previous to nextID
secondLastIDinGroup = ID previous to lastIDinGroup
'copy the data to the new chart
copy secondLastIDinGroup to new location
copy lastIDinGroup to new location
'increment current ID so that you'll process the next set of ID's
currentID = nextID
end loop

If anyone knows of a 'slick' way of picking out the data Utkarsh is
interested identifying, I'd be interested in seeing it as well. In the
meantime, Utkarsh, the above pseudo-code should get you started in
programming a routine to solve your problem.

HTH,
tdw


--
Timothy White
Contract Programmer
Ontario, Canada

<my initials>hite<at>sympatico<dot><countryCode>

 

[Utkarsh]

Thanks tdw. This is what I came up with:

Sub re_organise()

Sheets("Sheet1").Select

i = 2
j = 2
Do While Cells(i, 1) <> ""
If Cells(i, 1) <> Cells(i + 1, 1) Then
Sheets("Sheet2").Cells(j, 1) = Cells(i - 1, 1).Value
Sheets("Sheet2").Cells(j, 2) = Cells(i - 1, 2).Value
Sheets("Sheet2").Cells(j, 3) = Cells(i - 1, 3).Value
Sheets("Sheet2").Cells(j, 4) = Cells(i, 2).Value
Sheets("Sheet2").Cells(j, 5) = Cells(i, 3).Value
End If
i = i + 1
j = j + 1
Loop

Sheets("Sheet2").Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


End Sub

I'm sure there are better and more elegant solutions especially since
I'm having to sort the data by ID and then by date for this to work.

Utkarsh