Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto formate additons to a wookbook
I have an existing work book with formulas, etc, that I need to update daily
with a few dozen new records. Problem is the new data is somewhat unformatted and contains about 14 more columns than my working file does. In the new data/text files...things like a street address are separated into house number - direction - streetname - suffix in separate columns. My workbook uses the data combined into a single column address...i.e... 2324 E MADISON ST The new daily data comes in a text file. I use "get external data" to bring the text into the workbook at the bottom of the sheet. The new text/columns/data is always formatted the same in the "Get External data" process My problem/question is what's the best way to "get external data" that would give me the additional columns in the correct position where I need them ...?... rather than everyday have to edit/modify/insert columns to due data combining functions on and then delete the original separate data columns that have been defined...so I can copy/paste into the on going workbook ? My working sheet has 21 columns...but my daily import has 26 columns because various data fields are broken into sub columns which I then need to combine...as in the address sample above ? Thanks, Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
auto formate additons to a wookbook
"RawData" sheet: target for GetExternalData
"Conversion" sheet: -Laid out like exactly like your "Work" sheet -Cell Formulas reference RawData sheet and combine/parse data as needed. Ignore stuff you don't need. for example, to concatenate address: '= RawData!A2 & " " & RawData!A3 & " " & RawData!A4' - Do the Import to RawData. - Calculate the Conversion sheet - Copy the *values* (and maybe formating) of the new data rows from Conversion to Work. - Clear/Delete "old" data from RawData (but leave formulas on Conversion intact for tomorrow) Note: RawData and Conversion could easily reside in a separate workbook from Work itself. -- HTH, George "Tim" wrote in message ... I have an existing work book with formulas, etc, that I need to update daily with a few dozen new records. Problem is the new data is somewhat unformatted and contains about 14 more columns than my working file does. In the new data/text files...things like a street address are separated into house number - direction - streetname - suffix in separate columns. My workbook uses the data combined into a single column address...i.e... 2324 E MADISON ST The new daily data comes in a text file. I use "get external data" to bring the text into the workbook at the bottom of the sheet. The new text/columns/data is always formatted the same in the "Get External data" process My problem/question is what's the best way to "get external data" that would give me the additional columns in the correct position where I need them ..?... rather than everyday have to edit/modify/insert columns to due data combining functions on and then delete the original separate data columns that have been defined...so I can copy/paste into the on going workbook ? My working sheet has 21 columns...but my daily import has 26 columns because various data fields are broken into sub columns which I then need to combine...as in the address sample above ? Thanks, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating time in Number Formate | Excel Discussion (Misc queries) | |||
Formate colour of cells in a range of raw | Excel Discussion (Misc queries) | |||
Formate colour of cells | Excel Discussion (Misc queries) | |||
No Auto date formate | Excel Discussion (Misc queries) | |||
cell formate | Excel Discussion (Misc queries) |