Need help with changing data after it has been imported.

[Guest]

I have posted this similar questions before and tried the suggested answer
with no success.

I am importing a large amount of data to be sorted and placed in a pivot
table. One of the fields being sorted from the data is a user input, by
multiple users. Well all the user don't call the same things by the same
names. Ex. User John puts in "Comp.". User Jane puts in "PC". What I want
the field to say is "Computer". Someone had suggested to use the autocorrect
tools, the problem is I can't modify the input program, I have to pull the
data the way the user inputs it and then I end up changing it all manually.

If there is anyway I can define a list like the autocorrect options or a
macro doing the same thing, I would be very thankful if anyone had any good
ideas on how to accomplish this.

Thanks,

 

[xLBaron]

How about trying to use the "List" function. This way you can only
choose from the designated List of items. You can always update the
list when a new item is added:


1st - On a separate tab that you can hid later put the data that you
want in a column, like this:
A1= Computer
A2= Keyboard
A3= Monitor
A4= Mouse
A5= .... you can keep adding .....

Now on your top Toolbar go "Insert" ~> "Name" ~> "Define"


Define Name Box will open
In the "Names in workbook" let's type "Data" for this purpose. Go to
the bottom of the box to "Refers to:" and select the data you want to
appear in the list box ... will look something like this
"=Sheet2!$A$1:$A$6"


Finally, on the right side of the box click "Add" and then "Okay"


2nd
Go to the tab and cell with you want the list box
Now on your top Toolbar go "Data" ~> "Validation" and "Settings" tab


In the "Allow:" box select "List" then in the "Source:" box type
=Data (if you choose another name when you defined the data then
it will be ="Your Name"


Click okay and your set

 

[Guest]

Thanks for the help, but this list command creates a drop down box which will
only allow the user to insert values that are on the list, my problem is a
little different, I am dumping data in the the excel spreedsheet from another
program that I cannot modify. I would like to autocorrect, autoformat on of
the fields so I don't have to go through the different groups and manually
change them.

If the users was entering the data into the excel spreedsheet, the list
command would be perfect. Thanks for the advise.

 

[steven1001]

You could try maintaining a list of what people actually enter and put
what you want it to be in the next column. eg

Col A Col B
Comp Computer
PC Computer
Computer Computer ... etc

Then you could do a vlookup against Col A based on what the user had
entered and populate another column in your data with the looked up
value from Col B, then use that value in the pivot table.

regards....

 

[Guest]

This looks like an excellent idea.

Thanks steve1001.