Another EXCEL question for power users

[Helpless]

I have a *bazillion* lines of data and I would like to clean up the acronyms....For Example,

+SMCSS ORG FRTSD LSGR 13.25OZ
+BARON STB SLS JAM LSGR 16OZ
+SMCSS CHRY FRTSD LSGR 13.25OZ
+SMCSS GRP FRTSD LSGR 13.25OZ
+SMCSS ORG FRTSD LSGR 13.25OZ


...I could use the spell checker to fix 'change all' a million times, but seems like there might be a better=easier way to do this...I have an enormous amount of data, so the spell checking route doesn't seem plausible.

...any macros I could use to auto-replace, or something like that? ideas? Suggestions? Anyone? anyone? Beuller? Anyone?

 

[Workin']

How about Edit -> Replace? I guess that's kinda like using tthe spell checker...

 

[Helpless]

Yea, for 30,000+ lines in each dataset and with about 60-70 sets, that might take awhile...was thinking there are some downloadable macros you can setup and auto-replace when it comes across a word (like FFR, etc)...

 

[Workin']

I'm guessing you mean something that can do an edit..replace on more than one pair at a time. Haven't seen anything like that. But that doesn't mean there isn't such a thing....

 

[meteu76]

Did you try "replace all"?

 

[Helpless]

Yea, replace all is the method I'll have to use, but I have a list of a ton of acronyms I'll keep probing and see what's out there...

 

[BenG3000]

Hi-

You can write your own macro to do this quite easily. It may be slow but it will be automated.

Hit Alt-F11 to bring up the VB Code window. I'll give you code to get you started:

Sub DoReplace
dim wkSheet as WorkSheet
dim i as Long

For each wkSheet in ThisWorkBook.Worksheets 'loop through all worksheets
For i = 1 to 10000
'may want to loop though columns here too
wkSheet.Cells(i, "A&quot = replace(wkSheet.Cells(i, "A&quot , " ORG ", " Organization &quot
'do all other replaces here
Next i
Next wkSheet
End Sub

You may want to insert a Code Module, then you can export it into to other Spreadsheets as needed

 

[Helpless]

Thanks, Ben..that's sounds exactly like what I am trying to do...but I am an Economist and not a VB guy, so I might have to post a couple questions when I try this Monday thanks to everyone...I'll give it a shot.

 

[Bglad]

There is a wizard for writing macros, you don't have to use the VB window. Writing macros is tedious but it may be worth it for what you are doing.

 

[Helpless]

Okay, i think I have figured out a pretty easy way to 'auto-correct' everything Thanks for the help.


Now, however, before I cross that bridge, I have to do one more thing that seems to be taking me forever:

I need to go line-by-line and extract only certain entries that contain things like FFR (Fat free), L/FT (Low-Fat), etc, etc...with 30,000+ lines in each dataset, it's taking me awhile, to say the least. Any thoughts on the best way to search the file for these letter combinations and extract them to create a seperate file? For example, I would like to take the lines with FFR, L/T, 2%FT, etc...and extract them to another file...make sense?

 

[Doomer]

Do a backup FIRST !!!!!!!

If you're familiar with MS Access, you could import your data into Access tables and use Queries to play to your hearts content. Then export back to Excel. What you're trying to do is easy as pie in Access.

 

[Helpless]

Thanks, Doomer...called a friend of mine who teaches VB online and he said he wouldn;t even mess around with VBA (sic?)....said to do exactly what you did


I usually get datasets that are already completed and ready to work with, but since I have to extract the data myself from 13,000+ supermarkets, I am trying to find the easiest way to do things


Thanks again...I'll give it a shot!

 

[RayH]

Putting everything in a database will help in managing the large datasets but if you need to do multiple search and replaces on the same record then I don't think there's any way to avoid using some type of looping programming logic.

A vb loop using Excel's substitute function would work but you would still have to check the same cell multiple times to find all the matches.

I did a search and found the utility at the bottom of this page which seems to do multiple search and replaces on multiple files at once. It looks like you'll need to cough up $15 though to use the batch mode.

 

[CodeJockey]

Helpless,

My 2 cents...

This looks like a job for...a programmer.
But, if you are going to tackle it then...

First, save a copy of your original data, in case something goes truly haywire and Undo doesn't work (it's been known to happen)...

For the search and replace, if you decide to go the VB route, try using the Excel "Record Macro" capability...turn on record, highlight about 5 lines of data, do the search and replace, then turn off record (be sure to give it a name), and then edit the code to change the Select section so that it selects the entire file. Excel writes VB macro code to do exactly what you did, so this is a very easy way to learn the VB code. You can write another function, in the same file as the recorded one, that will just change a couple of strings (for example, STR_SEARCHTEXT and STR_REPLACETEXT) to the values you want, then call the recorded function to perform the replace thoughout the file.

To extract the lines, try using the Sort capability to sort the lines according to the contents of a particular column. Then, scroll down to the section with the data you want, and highlight the lines, which should all be grouped together as a result of the sort.


Where is this data from (there isn't any single chain I know of with that many stores)? Why did you, not a programmer obviously, get stuck with this task?

 

[Helpless]

...it's data from more than 13K supermarkets with aggregate sales of more than $1M; And I got "stuck" with it being an economist for the Dept. of Agriculture We only do this study every 4-5 years, and the data is just in one main database, so I need to go in, extract it, play with a little, doing some voodoo, and write a book


Thanks for the ideas...I'll play around some more.

 

[CodeJockey]

Well, normally I would say that I could help for a "nominal" (A.K.A. Outrageous) hourly fee

but, since you work for the Govt., it would take forever to get approved, and they wouldn't pay such fees anyways (at least, not intentionally).


Good luck.

 

[Helpless]

>Well, normally I would say that I could help for a &quot;nominal&quot; (A.K.A. Outrageous) hourly fee <

I got it, thanks Thanks to everyone who helped me out and provided suggestions...for free As for your nominal fee, you are saving the lives of millions of people and increasing the human lifespan, since the analysis I will generate on the market share/price premium/cost-benefit analysis of nutritionally improved food markets will be given to manufactures, who will adjust their production resources accordingly

 

[Helpless]

...which is better than a &quot;nominal fee.&quot;


Thanks again to everyone.

 

[CodeJockey]

I wasn't hinting for a &quot;Thank you&quot;, or a fee...I was just kidding.

My payment in answering your questions comes when someone else answers my previous or future questions. It's a lot like a barter economy, with information as the currency.

Hmm, I'd better shut up, or your Govt. buddies will start thinking up ways to tax us.

Good luck with the research, hopefully it will be used by the manufacturers for good, and not for evil.