Asign value if partial text match - please help

[Trgovec]

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:
[TABLE="width: 183"]
<TBODY>[TR]
[TD]The Data list (Column A):
ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]ACTION SPORTS FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL ACC HW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP GENERIC </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL APP LICENSED </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
[TR]
[TD]FOOTBALL FTW </SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Firefly2012]

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]

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

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

 

[Trgovec]

Hey guys. Thank you so much for your quick reply.

Would it be possible to achive this without VBA? Maybe just with Excel functions.

Please find a sample file here:

Book1.xlsx - Speedy Share - upload your files here

Please also find the outcome here:

Outcome.xlsx - Speedy Share - upload your files here

 

[Firefly2012]

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
	A	B	C
1	Data	KEY	Categories
2	ACTION SPORTS FTW	action sports	football
3	ACTION SPORTS FTW	action sports	action sports
4	ACTION SPORTS FTW	action sports
5	ACTION SPORTS FTW	action sports
6	ACTION SPORTS FTW	action sports
7	ACTION SPORTS FTW	action sports
8	FOOTBALL ACC HW	football
9	FOOTBALL ACC HW	football
10	FOOTBALL ACC HW	football
11	FOOTBALL ACC HW	football
12	FOOTBALL ACC HW	football
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(2^15,SEARCH($C$2:$C$3,A2),$C$2:$C$3)

 

[Trgovec]

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]

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.