| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Raw data output is poor excel format part 1
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
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 "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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
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. "Joel" wrote: 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 "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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Raw data output is poor excel format part 1
Brilliant! Thanks Dave
"Dave Peterson" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you format data that is part of a drop down menu? | Excel Discussion (Misc queries) | |||
| Performance in excel 97 is poor | Excel Discussion (Misc queries) | |||
| format cell from data input to output form | Excel Worksheet Functions | |||
| excel data label format special number characters (part 2) | Excel Discussion (Misc queries) | |||
| Changing the format of an Excel output file made by Microsoft Access | Excel Discussion (Misc queries) | |||
Linear Mode
