Raw data output is poor excel format part 1

[aquaxander]

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.

 

[Joel]

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]

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]

Sub FixSiteName()

'create new column
Columns("A").Insert
LastRow = Range("B" & Rows.Count).End(xlUp).Row

Site = ""
FindSite = True
RowCount = 1
Do While RowCount <= LastRow
Data = Range("B" & RowCount)
Select Case Data
Case "Sum:"
Range("A" & RowCount) = Site
FindSite = True

Case ""

Case Else
If FindSite = True Then
Site = Data
FindSite = False
Else
Range("A" & RowCount) = Site
End If
End Select

RowCount = RowCount + 1

Loop
End Sub

 

[Dave Peterson]

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]

That's brilliant! Thanks Joel.
I threw the macro in and it worked a treat.
I would like to know how you did it though? If you could advise me (so that
I can learn) it would be much apperciated.

 

[aquaxander]

Brilliant! Thanks Dave

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.)