rearranging data

[SandsB]

1111 Bob Salesman red 1/1/2007
1111 Bob Salesman blue 1/2/2007
2222 Mike Salesman red 1/1/2007
3333 Joe Manager green 8/24/2007
1111 Bob Salesman green 4/3/2007
2222 Mike Saleman green 6/1/2007
3333 Joe Manager red 5/31/2007

Basically, that's my data as it comes to me - the first column is the sale number. I need a trick that'll read the original data and then put everything for each unique sale number on a unique line in a new file by appending the date (Column E) and whatever is in column D to columns on the right in date order. So the end result would look like this:

1111 Bob Salesman red 1/1/2007 blue 1/2/2007 green 4/3/2007
2222 Mike Salesman red 1/1/2007 green 6/1/2007
3333 Joe Manager red 5/31/2007 green 8/24/2007

Anybody know how to do this?

 

[lenze]

It would take a lot of code to do it that way. Your data, however, is in ideal format as it comes to you. Just use a Pivot table to get the reports you need. An example. a Pivot Table can turn this

  A        B    C        D     E         
1 Sales No Name Position Color Date      
2 1111     Bob  Salesman red   1/1/2007  
3 1111     Bob  Salesman blue  1/2/2007  
4 2222     Mike Salesman red   1/1/2007  
5 3333     Joe  Manager  green 8/24/2007 
6 1111     Bob  Salesman green 4/3/2007  
7 2222     Mike Saleman  green 6/1/2007  
8 3333     Joe  Manager  red   5/31/2007 

Sheet1

[Table-It] version 07 by Erik Van Geit

into this

   G              H         I    J        K     L     
 7 Count of Color                                     
 8 Sales No       Date      Name Position Color Total 
 9 1111           1/1/2007  Bob  Salesman red   1     
10                1/2/2007  Bob  Salesman blue  1     
11                4/3/2007  Bob  Salesman green 1     
12 1111 Total                                   3     
13 2222           1/1/2007  Mike Salesman red   1     
14                6/1/2007  Mike Saleman  green 1     
15 2222 Total                                   2     
16 3333           5/31/2007 Joe  Manager  red   1     
17                8/24/2007 Joe  Manager  green 1     
18 3333 Total                                   2     
19 Grand Total                                  7     

Sheet1

[Table-It] version 07 by Erik Van Geit

There are literally hundreds of ways to vary your table depending on your needs.

lenze

 

[SandsB]

I see what you're saying but my issue is that I need it in the format requested by poeple much higher than me. There are other macros written by people long gone (and much smarter than me) that depend on this format. Right now, I manually do the conversion and it sucks up a lot of time.