Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Hi Rich-
One thing you might want to take a look at is the Advanced Filter located in DataFilter. 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 , "Rich" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep file sizes small when using multiple pivot tables? | Excel Discussion (Misc queries) | |||
How to pull numbers from two tables with conditions? | Excel Worksheet Functions | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Combining Tables | Excel Discussion (Misc queries) | |||
How to paste Xcel tables legibly into Word . Help!! | Excel Discussion (Misc queries) |