auto formate additons to a wookbook

[Tim]

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

 

[George Nicholson]

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