lookup macro

hyoung · Apr 23rd, 2002, 04:17 AM

new-ish excel user so apologies if answer is obvious!

I need to take a 140 column comma delimted file, import into Excel, assign new values to 30 of the columns based on a lookup (e.g. if column entry = Mr, replace with CG1254SFV), then export it again as a comma delimited file with "" marks around each value.

And I need to make this an automated procedure using a macro

Is this something I can do in Excel?

Any feedback greatly appreciated.

jimbojones · Apr 23rd, 2002, 06:18 AM

Haven't tried it myself but surely just import the file. Start recording a macro. Run text to columns. Input lookups. Save as file type .CSV (for comma delimited).

Does this work?

Jim

hyoung · Apr 23rd, 2002, 06:55 AM

Thanks Jimbo - think I gave too much info in the problem. Happy with the import, report macro and save as csv. Where I was coming from is the lookup side of things. The worksheet will have 140 columns, 40 of which will have values that I need to change, and each column might have 100+ different possibilities for the lookup, e.g. occupation. I have a table of occupation codes and I would need to change all the data in the column, e.g. mechanic becomes CVF1254DF etc. As I need to do this for 30 columns, I was looking for the best way of carrying this out?

Hope this is clear, fear it is not

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 23rd, 2002, 04:17 AM	#1
hyoung New Member Join Date: Apr 2002 Location: Suffolk, UK Posts: 2	new-ish excel user so apologies if answer is obvious! I need to take a 140 column comma delimted file, import into Excel, assign new values to 30 of the columns based on a lookup (e.g. if column entry = Mr, replace with CG1254SFV), then export it again as a comma delimited file with "" marks around each value. And I need to make this an automated procedure using a macro Is this something I can do in Excel? Any feedback greatly appreciated.

Apr 23rd, 2002, 06:18 AM	#2
jimbojones Board Regular Join Date: Apr 2002 Location: Channel Islands, Britain. Posts: 774	Haven't tried it myself but surely just import the file. Start recording a macro. Run text to columns. Input lookups. Save as file type .CSV (for comma delimited). Does this work? Jim

Apr 23rd, 2002, 06:55 AM	#3
hyoung New Member Join Date: Apr 2002 Location: Suffolk, UK Posts: 2	Thanks Jimbo - think I gave too much info in the problem. Happy with the import, report macro and save as csv. Where I was coming from is the lookup side of things. The worksheet will have 140 columns, 40 of which will have values that I need to change, and each column might have 100+ different possibilities for the lookup, e.g. occupation. I have a table of occupation codes and I would need to change all the data in the column, e.g. mechanic becomes CVF1254DF etc. As I need to do this for 30 columns, I was looking for the best way of carrying this out? Hope this is clear, fear it is not