Put this in B1:
=A1

Put this in B2:
=IF(A3="",A2,B1)
and drag down the column.

Convert column B to values.
Add headers to column A and B
Sort by column A (to put the dates together -- and the text values can be
deleted easily.
Resort by column B (and column A as secondary key) if you want

And create the pivottable.

I'd do it against a copy to make sure it works the way you want.

(You may be able to use data|filter|autofilter to eliminate the "bad" records.)

aquaxander wrote:

A1 Vehicles

03-Jan-09
05-Jan-09
06-Jan-09
07-Jan-09
08-Jan-09
09-Jan-09
10-Jan-09
12-Jan-09
13-Jan-09
14-Jan-09
15-Jan-09
16-Jan-09
18-Jan-09
19-Jan-09
20-Jan-09
21-Jan-09
22-Jan-09
23-Jan-09
24-Jan-09
26-Jan-09
27-Jan-09
28-Jan-09
29-Jan-09
30-Jan-09
Sum:
Abbey Service

07-Jan-09
08-Jan-09
09-Jan-09
11-Jan-09
12-Jan-09
15-Jan-09
18-Jan-09
19-Jan-09
20-Jan-09
21-Jan-09
24-Jan-09
25-Jan-09
26-Jan-09
28-Jan-09
Sum:
ABC Motors

02-Jan-09
05-Jan-09
08-Jan-09
09-Jan-09
11-Jan-09
12-Jan-09
13-Jan-09
15-Jan-09
19-Jan-09
21-Jan-09
23-Jan-09
29-Jan-09
30-Jan-09
Sum:

"Joel" wrote:

Can you post some sample of the text lines? Are you Site Names multiple
words? What is the format of the dates. There are many formats that match
your description and each will require a slightly different approach.

"aquaxander" wrote:

Hi,
I have data output from a different source which when it is placed in excel,
the first column (from up to down) has a site name, then a blank, then
certain dates in january (not all dates only those that have data relating to
it), then a "sum:" row, then the name of the next site and so on. This makes
it awkward to do pivot tables, etc. There are over 1,000 sites. If I insert a
column, can I somehow add the site name next to it's relevant 'dates' section
using a formula? Any other suggestions would be greatly received.

--

Dave Peterson