Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearranging data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearranging data
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 initialshite<atsympatico<dot<countryCode "Utkarsh" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
rearranging data
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(xlCellT ypeBlanks).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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rearranging DATA | Excel Discussion (Misc queries) | |||
Rearranging data | Excel Discussion (Misc queries) | |||
rearranging data | Excel Worksheet Functions | |||
Rearranging Data Help... | Excel Discussion (Misc queries) | |||
rearranging data | Excel Programming |