Excel Data Manipulation - Is this possible? 1

[notadba]

Hi,

I have a large dataset that I need to change to suit a client - and doing it manually is too cumbersome.

I have a 30k+ row spreadsheet that has multiple rows of data per person - ranging between 1-25 rows per ID, with a simplified structure like:

ID date_from Date_to level etc
1 01/01/1998 15/07/2001 E1
1 16/07/2001 12/06/2003 E2
1 13/06/2003 current_date E2
2....

Need to run a script to compare the ID in column 1, and where matching, pivot the date_form, date_to and level out to new columns for the first row/instance of the ID. As an added nicety, removing the now redundant rows would be good - though this can be easily done with a formula/sort afterwards.

In the 'worst' case this may mean another 75 columns to be created.

There probably is an excel function that I am not aware of, but not sure if anything delivered can do somthing this complex.. any assistance much appreciated.

Thanks

 

[CautionMP]

notadba,
Somewhere in the vast array of built-in Excel functions there is probably a mechanism for doing this and that would be the right way to go.

Becasue the difference between done right, and done right Now is a couple of paychecks, this might get you started. This will take data in the format you described from [tt]Sheet1[/tt] and create a converted copy of it on [tt]Sheet2[/tt]

Sub PivotData()
Dim wksIn As Worksheet, wksOut As Worksheet
Dim lngRowIn As Long, lngRowOut As Long, lngColumnOut As Long
Dim lngID As Long
Set wksIn = Worksheets("Sheet1")
Set wksOut = Worksheets("Sheet2")
For lngRowIn = 2 To wksIn.UsedRange.Rows.Count + 1
  'Determing if the same id and set the output positions
  If wksIn.Cells(lngRowIn, 1) <> lngID Then
    'New ID so reset the pointers
    lngID = wksIn.Cells(lngRowIn, 1)
    lngRowOut = lngRowOut + 1
    lngColumnOut = 2
  Else
    'Same id so index the pointer
    lngColumnOut = lngColumnOut + 3
  End If
  'Write the data and copy the formats
  wksOut.Cells(lngRowOut, 1) = lngID
  wksOut.Cells(lngRowOut, lngColumnOut) = wksIn.Cells(lngRowIn, 2)
  wksOut.Cells(lngRowOut, lngColumnOut).NumberFormat = wksIn.Cells(lngRowIn, 2).NumberFormat
  wksOut.Cells(lngRowOut, lngColumnOut + 1) = wksIn.Cells(lngRowIn, 3)
  wksOut.Cells(lngRowOut, lngColumnOut + 1).NumberFormat = wksIn.Cells(lngRowIn, 3).NumberFormat
  wksOut.Cells(lngRowOut, lngColumnOut + 2) = wksIn.Cells(lngRowIn, 4)
  wksOut.Cells(lngRowOut, lngColumnOut + 2).NumberFormat = wksIn.Cells(lngRowIn, 4).NumberFormat
Next lngRowIn
Set wksOut = Nothing
Set wksIn = Nothing
End Sub

And remember there is never as much time to develop as there is to re-develop

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Hi,

current_date is going to be a nightmare in your dataset. you ought to do an Edit/Replace, replacing with
[tt]
=TODAY()
[/tt]
Any other place in the date columns that does NOT have a REAL DATE, needs to be replaced with a REAL DATE.

What's the purpose of doing this transform?



Skip,

_[red][/red]

 

[notadba]

Thanks for the quick responses-

CMP - I will give it a shot with modifications to cater for the other 20 columns of data that I have in the result set. I am sure I will be back with questions as I am complete noobie with vb.

Skip,

The purpose of this is to manipulate query output into a single row to suit a client who has a system defined to only take input this way (!!!!). The current date can be any day in the future as it come from a transactional system that only records 'as of date' information, and I have derived the end date - current date is for open ended or 'current' state. The data is who is where and at what level within the organisation over time. Will look at a better date option...

Thanks again.