Index/match type function that matches partial entry with a data set and spits out the general category

pailmonkey

New Member
Joined
Jun 4, 2018
Messages
1
I ran into another issue while trying to solve something else. I have a table of subcategories and general categories that i would like to be able to match to as close as possible to the general category. There are two parts to this issue. One is that the cell where the table will be matching is not uniform. I am exporting data from multiple companies and each one has slight variations to how they display the data. What i am trying to do is after you import the data for the spreadsheet either match directly to the general category or to match the sub category and then match to the general. Due to all of the variations there could be, i figured that matching to the sub category and then doing a simple vlookup could work but i cannot seem to get it to work.

Below is the data.

Data Import Sheet


The Cause of Injury is the data set that will be imported from company to company. The Cause Of Injury category is what the general category should be. What i am trying to do is automate as much as possible the matching if i import a data set to a corresponding general category. For example, one set of data may have "Fall, Slip, or Trip into Opening" another may be "Fall or Slip in to Openings" or "Fall/Trip into Openings" or some variation. I want to recognize that this means "Fall, Slip, or Trip Injury Into Openings" and then spit out "Fall, Slip, or Trip Injury" in the Cause of injury Category Lookup solution. Or just know that this variation falls into the general category "Fall, Slip, or Trip Injury". What is the best way to solve this? It sounds almost like need excel to match my requested cell with the sub to as similar as possible and then spit out the general. Like the text that you entered is 80% similar to this cell in the list. Then it will display the general category from the category list in the Cause of Injury Gen Category column.


Here is the entire table of possible and their general category. There are many sub categories to a general categories but the bigger issue is the variance between company to company to how they enter the subcategories. Either i manually enter the General Category with each cell or somehow automate it.


Category List
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Cross posted https://stackoverflow.com/questions...hes-partial-entry-with-a-data-set-and-spits-o

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top