Macro help to rearrange excel table

[Alan]

I sure hope one of you macro gurus can help me...

I have a 3 column list that gets imported from an ascii file that I need to
manipulate. Here's a source sample:


NAME RM# Date
BOB SMITH 204 28-Jan-06
BOB SMITH 204 28-Feb-06
BOB SMITH 204 14-Mar-06
BOB SMITH 204 10-Apr-06
ALLAN JONES 202 28-Jan-06
ALLAN JONES 202 28-Feb-06
ALLAN JONES 202 14-Mar-06
ALLAN JONES 202 10-Apr-06
ISABEL PERKINS 205 28-Jan-06
ISABEL PERKINS 205 28-Feb-06
ISABEL PERKINS 205 9-Mar-06
ISABEL PERKINS 205 14-Mar-06
ISABEL PERKINS 205 10-Apr-06


What I need to do is rearrange this data so that there is 1 row per unique
name and create columns for the dates in the order they appear in the list.
Having it done on another worksheet would suffice.

Headers would be:

NAME, RM#, Date1, Date2, Date3, Date4, Date5.....

Using the sample above it would look like:

NAME RM# DATE1 DATE2 DATE3 DATE4
BOB SMITH 204 28-Jan-06 28-Feb-06 14-Mar-06 10-Apr-06
ALLAN JONES 202 28-Jan-06 28-Feb-06 14-Mar-06 10-Apr-06


There may be more dates for some people, so a check for the name change
should drive the rearrangement.

Is anyone up for the challenge??

TIA, Alan

 

[Jim May]

With your first data record info in A4:C4, in
D4:H4 enter:
=COUNTIF($A$4:$A4,A4) =IF(D4=1,C4,"")
=IF($D5=2,OFFSET($D5,0,-1),"") =IF($D6=3,OFFSET($D6,0,-1),"")
=IF($D7=4,OFFSET($D7,0,-1),"")
Copy down All 5 formulas as far down as is needed.
Hide Columns C
This is One way...
Above only allows for 4 dates - you can create more based on the examples
given..

HTH
Jim May