rearranging data

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
616
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
Code:
  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
Code:
   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

Well-known Member
Joined
Feb 13, 2007
Messages
616
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top