#### Trgovec

##### New Member
Hello,

i often find your forum when i am looking for answers regarding Excel. Sadly, i havent found an answer to this one - yet, so i have decided to write to you.

I have a long list of categories in the column A (seen below). To this list i want to assign keys that will help me with further calculations (in a column B).

An example of such a key would be in the case of the given example "ACTION SPORTS" and "FOOTBALL". The whole list of these "keys" hast 17 elements.

It is evident that i will need some kind of a partial match. The one i have found on the internet, limittin itself just on the first or last number of characters wont work.

Would it be possible to select my "key - list" (all 17 of them). Then excel would asign a key, whenver it would find a partial match, matching the key?

Thank you very much for your help.

I have been looking for a solution for day and it's becomming quite urgent now :/

Kind regards,

Trgovec

-----------

Appendix:
 The Data list (Column A): ACTION SPORTS FTW ACTION SPORTS FTW ACTION SPORTS FTW ACTION SPORTS FTW ACTION SPORTS FTW ACTION SPORTS FTW FOOTBALL ACC HW FOOTBALL ACC HW FOOTBALL ACC HW FOOTBALL ACC HW FOOTBALL ACC HW FOOTBALL ACC HW FOOTBALL APP GENERIC FOOTBALL APP GENERIC FOOTBALL APP GENERIC FOOTBALL APP GENERIC FOOTBALL APP GENERIC FOOTBALL APP GENERIC FOOTBALL APP LICENSED FOOTBALL APP LICENSED FOOTBALL APP LICENSED FOOTBALL APP LICENSED FOOTBALL APP LICENSED FOOTBALL APP LICENSED FOOTBALL FTW FOOTBALL FTW FOOTBALL FTW FOOTBALL FTW FOOTBALL FTW FOOTBALL FTW

<TBODY>
</TBODY>

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Firefly2012

##### Well-known Member
I'm afraid I don't understand what you want to achieve.

Please post examples of what you have now and what you want to result in, including useful details of how the start value relates to the result value.

#### nuked

##### Well-known Member
Something like this perhaps? If not, can you clarify your requirement by giving a list of keys, and the outcome you desire.

Code:
``````Option Explicit

Sub AssignKeys()

'# This assumes your data is in column A, that the assigned keys
'# will be put next to the data in column B, and the list of possible
'# keys is in column C.

Dim rngData As Range
Dim rngKeys As Range
Dim cel As Range
Dim rng As Range
Dim vTmp As Variant
Dim i As Long

Set rngData = Range("A:A")
Set rngKeys = Range("C:C")

For Each cel In rngData

If Not IsEmpty(cel) Then

vTmp = Split(cel, " ")

For i = LBound(vTmp) To UBound(vTmp)

For Each rng In rngKeys

If Not IsEmpty(rng) Then

If rng = vTmp(i) Then cel.Offset(0, 1) = rng

Else

Exit For

End If

Next rng

Next i

Else

Exit For

End If

Next cel

End Sub``````

#### Firefly2012

##### Well-known Member
Assuming you want to return the key based on a partial match (and not include an incremental number within the key) then you can use this:

Excel 2010
ABC
1DataKEYCategories
2ACTION SPORTS FTWaction sportsfootball
3ACTION SPORTS FTWaction sportsaction sports
4ACTION SPORTS FTWaction sports
5ACTION SPORTS FTWaction sports
6ACTION SPORTS FTWaction sports
7ACTION SPORTS FTWaction sports
8FOOTBALL ACC HWfootball
9FOOTBALL ACC HWfootball
10FOOTBALL ACC HWfootball
11FOOTBALL ACC HWfootball
12FOOTBALL ACC HWfootball
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(2^15,SEARCH(\$C\$2:\$C\$3,A2),\$C\$2:\$C\$3)

#### Trgovec

##### New Member
Thank you ever so much!!

That's exactly what i need. Now, would you also please be so kind and try to explain to me how it works.

The bit i don't understand is "2^15". Why that? If i have a database with 7000 rows, do i have to change this?

Thank you once again.

Kind regards

#### Firefly2012

##### Well-known Member
The 2^15 simply returns a number that will be greater than any return of the SEARCH function (cell contents are limited to 32,767 characters, 2^15 returns 32,768). This ensures that, assuming a partial match is found, it will always be returned.

You won't need to alter this, although often you will see the number 9.9E+307 used instead (it's a vvvvvvvvvv large number, close to the max permissible in Excel) based on exactly the same principle.

Replies
5
Views
411
Replies
0
Views
143
Replies
1
Views
373
Replies
1
Views
2K
Replies
6
Views
523

1,195,591
Messages
6,010,614
Members
441,558
Latest member
lambierules

### 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.

### Which adblocker are you using?

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

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