Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I have a 174,000 row excel spreadsheet of different business with their business information as well as attributes that describe each business. I am trying to categorize the businesses by issuing a number to each type of business with regards to the attributes column. For example businesses with "restaurant" as an attribute would be given a "1", "health" would be denoted as "2" and so on with 7 total categories. I was wondering if there is anyway to substitute or replace these attribute words with their designated numbers?
Solved by F. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/07/2018 - 07:14
Hi
Excelchat Expert
11/07/2018 - 07:14
Welcome to Got It Pro.
Excelchat Expert
11/07/2018 - 07:15
Please share the sample list in the preview.
User
11/07/2018 - 07:16
Hi, I am trying to upload a sample of the work but the file is so large
Excelchat Expert
11/07/2018 - 07:16
Please include a small sample in preview.
Excelchat Expert
11/07/2018 - 07:16
As per the requirement, you wan to assign number for each item.
Excelchat Expert
11/07/2018 - 07:17
I can provide an example of that using vlookup function? Please confirm my understanding?
User
11/07/2018 - 07:17
Yes that would be helpful
User
11/07/2018 - 07:18
I am trying to upload a sample for you
Excelchat Expert
11/07/2018 - 07:18
I have provided sample in sheet2.
Excelchat Expert
11/07/2018 - 07:18
There are list of categories and numbers.
User
11/07/2018 - 07:19
Yes, I have included the categories I am working with
Excelchat Expert
11/07/2018 - 07:20
If it is a large list, then it is better to go with vba programming.
Excelchat Expert
11/07/2018 - 07:20
For one item, i can use vlookup formula.
Excelchat Expert
11/07/2018 - 07:20
Please refer sheet2 to use vlookup for one item.
Excelchat Expert
11/07/2018 - 07:21
Input is in E1 cell and the result for health is 2.
Excelchat Expert
11/07/2018 - 07:21
If cell has large number of entries, it is not easy to deal with formulas.
Excelchat Expert
11/07/2018 - 07:22
You have to go with vba programming. We are not supporting vba programming at the moment. However, we may resume services soon.
User
11/07/2018 - 07:22
ok is there anyway I can take all the entries in the attribute section and replace it with just one word like you did in sheet 2 and then use vlookup
Excelchat Expert
11/07/2018 - 07:23
There is way for this. However, you have to use split columns separated by commas.
Excelchat Expert
11/07/2018 - 07:23
But, at the end, they all will be in different columns
Excelchat Expert
11/07/2018 - 07:24
There are few steps, you can do by the way.
Excelchat Expert
11/07/2018 - 07:24
1) Replace all single quotes to blank using find and replace
Excelchat Expert
11/07/2018 - 07:24
2) Replace "]" and "[" with blanks.
Excelchat Expert
11/07/2018 - 07:24
3) Now, you will left with categories separated by commas.
Excelchat Expert
11/07/2018 - 07:25
4) Then, you can use text to columns feature under data menu.
Excelchat Expert
11/07/2018 - 07:25
5) Then all categories in a cell will be splitted to each column. You can find and replace each category.
Excelchat Expert
11/07/2018 - 07:26
However, a suggestion is to go with vba programming.
User
11/07/2018 - 07:26
how do i do that?
Excelchat Expert
11/07/2018 - 07:26
We are not vba programming at the moment. It is vba excel programming. So, we can automate all these using macros.
User
11/07/2018 - 07:27
okay thank you for your help
Excelchat Expert
11/07/2018 - 07:27
If it is a single entry, vlookup is easy to use.
Excelchat Expert
11/07/2018 - 07:27
Thank you.
Excelchat Expert
11/07/2018 - 07:27
Please do visit Got It Pro, for any advise or formula help.
User
11/07/2018 - 07:28
ok thank you again!
Excelchat Expert
11/07/2018 - 07:28
Have wonderful day ahead.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
