CODEWITHSUNDEEP
excelexcel-formulavba
user1087661
user1087661

Reputation: 101

Categorization Based on Text-Code in Columns

I have two columns with codes relating to various products. It is a 3-part code delimitized with a '-'. The length of each of the 3 parts are not constant and are alphanumeric.

The need is that I have to categorize them according to 4 criterias: Compare each of the codes in B against A and vice-versa, and categorize them all as below and in the image: 1. Exactly Matched codes 2. Prefix or Suffix Changes codes 3. Totally New codes

However there seems to be a complication. The codes in two columns are not necessarily sorted and there can be a match anywhere in the other column, Is there a way to look up for the text and then do the compare function. I know this opens up a lot of complications -- my thought is to look up the value, and then pass the parameters to get the category.. Thanks again!!! – user1087661 1 hour ago

Kindly help me achieve this. Is there any formula to check through an array and Find functions? many thanks for the support.

Code Categorization

Upvotes: 0

Views: 129

Answers (2)

Rosetta
Rosetta

Reputation: 2725

you can use split function and select case to deal with your problem. i assume you know how to use a UDF.

Function CompareCode(Text1, Text2, Optional Delim = "-")
    Dim T1, T2, CC
    T1 = Split(Text1, Delim)
    T2 = Split(Text2, Delim)
    CC = (T1(0) <> T2(0)) * 100 + (T1(1) <> T2(1)) * 10 + (T1(2) <> T2(2)) * 1
    CC = Format(-CC, "000")
    Select Case CC
        Case "000": CompareCode = "Same code"
        Case "100": CompareCode = "Prefix changed"
        Case "010": CompareCode = "Base changed"
        Case "110": CompareCode = "Prefix and base changed"
        Case "001": CompareCode = "Suffix changed"
        Case "101": CompareCode = "Prefix and suffix changed"
        Case "011": CompareCode = "Base and suffix changed"
        Case "111": CompareCode = "Totally new code"
        Case Else:
    End Select
End Function

Upvotes: 3

rohrl77
rohrl77

Reputation: 3337

This is merely a partial answer:

For the first part, the exactely matching codes, you can use a simple lookup formula such as SUMIFS() where you are matching the items in Column B to the whole set in Column A.

For the other two requirements, if I wanted to do this by formula, then I would use the LEN(), LEFT() and RIGHT() formulas to extract the prefix, base, and sufix into separate columns. Do this for both Group A and B.

Finding your matching groups should become fairly straight forward from that point on.

Upvotes: 0

Related Questions