Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rearrange the data with a new table | Excel Worksheet Functions | |||
need to take data from one table and rearrange columns into new ta | Excel Discussion (Misc queries) | |||
EXcel 2002: How to rearrange data table ? | Excel Discussion (Misc queries) | |||
How do i rearrange pie chart without re-arranging source table? | Charts and Charting in Excel | |||
Rearrange data columns in Pivot Table | Excel Discussion (Misc queries) |