Special sort for oldest and newest date

[Guest]

Hi!

I have a file that contains columns of:
SERIAL NUMBER, COMPANY, DATE of SALE, many other fields

In the end, I would like a report listing each company only once and showing
the oldest and newest date of sale (it would be nice to have the
corresponding serial numbers associated with the sale dat, but that's not
necessary).

131 Co_A 10/12/2005
222 Co_A 12/31/2005
123 Co_B 06/04/2006
555 Co_A 03/17/2006
324 Co_C 04/01/2006
299 Co_A 08/09/2006
484 Co_A 07/04/2006
814 Co_B 05/26/2005

Would like something like this:
Company Oldest Serial Newest Serial
Co_A 10/12/2005 131 08/09/2006 299
Co_B 05/25/2005 814 06/04/2006 123
Co_C 04/01/2006 324 04/01/2006 324

Is this possible without too much difficulty?

Thank you so much!

 

[Guest]

Here we go

PIVOT TABLES are what you need

1 add titles to your columns, and an extra column for the serial number

REF COMP DATE SERIAL
131 Co_A 10/12/2005 =C2-DATE(YEAR(C2)-1,12,31)
222 Co_A 12/31/2005 etc
123 Co_B 06/04/2006 etc

Format that serial number as a number (Format > Cells)

2 Click on cell A1 and do data > pivottable > next > finish

Drag company into the row field
Drag date into the space for data items
Right click where it says "count of DATE", do field settings and choose Max.
Format column B of your pivot table as a date.

Drag date into the space for data items again.
You will see a column called Data now.
Grab this column called data and drag it so that it is above the data area
(becoming a row not a column).
now you have 2 columns

right click on where it says Count of DATE, field settings, choose Min.

Now you have to drag in the SERIAL field twice, and set it as a max and a min.

you can change the order of the columns by selecting them and dragging them
around.