Converting to an efficient set of tables

I was handed down an Excel file consisting of 47,000 rows and 20+ columns of
data. In each column, there is an enormous amount of repetition. The file is
over 20 MB and runs slowly.

Because of reasons beyond my control at work, I cannot use MS Access to
handle this data. So, I would like to create several tables containing the
unique values that repeat so frequently and essentially structure the data
like I would in Access with various relationships between fields.

How can I (at least somewhat) automate this task to replace the cell values
with references to another cell on another sheet? For example, replace every
occurrence of "LOS ANGELES, CA" in a certain column to, perhaps, whatever
cell "LOS ANGELES, CA" exists in in Sheet 2, Column A. I could be wrong but
some sort of vlookup seems like it might work for this.

Thanks.

Note: I forgot to include this in my original post.

Another possible solution I thought of is to use the "Replace" feature in
the Edit menu. Tell it to search for "LOS ANGELES CA" and replace it with
=Sheet2!B4 (for example) where cell B4 in Sheet 2 contains the same value,
"LOS ANGELES CA". Would there be a way to perform hundreds or thousands of
these "Replace" actions through VBA rather than manually doing so one by one?
If so, I'd appreciate knowing how since I have no idea.

Hi Rich-

One thing you might want to take a look at is the Advanced Filter located in
Data>Filter. It can be used to copy unique records from the primary data
range to another location. Also, investigate the Database Functions, such as
DSUM, DMIN, etc. You'll probably find them quite useful.

I don't envy your situation! It sounds as though you are dealing with a
typical flat-file nightmare that really needs to be handled in a relational
program. Excel can be used somewhat more effectively than what the file
sounds like it has been set up for, but certainly not true a relational
environment.

Good Luck |:>)


On 8/6/05 4:29 AM, in article
148C70B1-B502-4EF3-BAF6-087290364B37@microsoft.com, "Rich"
<Rich@discussions.microsoft.com> wrote:

> Note: I forgot to include this in my original post.
>
> Another possible solution I thought of is to use the "Replace" feature in
> the Edit menu. Tell it to search for "LOS ANGELES CA" and replace it with
> =Sheet2!B4 (for example) where cell B4 in Sheet 2 contains the same value,
> "LOS ANGELES CA". Would there be a way to perform hundreds or thousands of
> these "Replace" actions through VBA rather than manually doing so one by one?
> If so, I'd appreciate knowing how since I have no idea.