Macro help to rearrange excel table
 

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

Macro help to rearrange excel table
 

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:D
This is One way...
Above only allows for 4 dates - you can create more based on the examples
given..

HTH
Jim May

"Alan" wrote:

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