CODEWITHSUNDEEP
excelexcel-formula
Deepak
Deepak

Reputation: 33

Find all rows which contain a substring within string and return first cells of all such rows in ms-excel

I have categorized certain ids. Now each id may belong to multiple categories. Let's say column A contains category names, column B has all ids that category contains. I add column C which has all ids ( a single id in a cell). Now i want to have in column D all category names which contain that id(from column C). IF that id is not found among categories(column A) then return "not found".

      A             B         C     D

  category A   a21|b43|g87   a21   category A 

  category B   b43|j78|n99   b43   category A category B

                             c35   not found

I have tried using Index, Match, iferror, find etc but not able to achieve my intended objective. Any help will be really appreciated.

Upvotes: 3

Views: 5434

Answers (1)

Ali NasserEddine
Ali NasserEddine

Reputation: 323

Have you tried a combination of "if" & "search" functions?

For just these two categories and assuming you don't have headers, you can use:

=IF(ISERROR(SEARCH(C1,B$1)),"",A$1) & " " & IF(ISERROR(SEARCH(C1,B$2)),"",A$2)

If you have 3 more categories, you can add them in the same manner (concatenation) while changing the rows for B & A in each if statement. If the ID is not found in any category, it will result in a blank cell. You can add another if statement upfront to return "not found" if it is required, or you can do it manually later by filtering blank cells in column D.

EDIT: If you have too many categories as stated (updated), then best option is to use a short VBA code. The concept is simple: for every ID you have, search all possible categories. Two "for loops" are sufficient. 

Sub FindID()
 lastRowID = Range("C" & Rows.Count).End(xlUp).Row 'Define number of IDs
 lastRowCAT = Range("A" & Rows.Count).End(xlUp).Row 'Define number of CATs

    For i = 2 To lastRowID   'For each ID
        Category = 0         'We reset the category for each ID
        currentID = Range("C" & i).Value 'store the value of the ID

            For j = 2 To lastRowCAT 'Looking the ID in each category
                If InStr(Range("B" & j).Value, currentID) > 0 Then 'If it is found, we add it to cateogries
                   Category = Category & " " & Range("A" & j).Value
                End If
            Next j

            If Category = 0 Then
                Range("D" & i).Value = "not found"
            Else
                Range("D" & i).Value = Right(Category, Len(Category) - 2)
            End If

    Next i

End Sub

Upvotes: 2

Related Questions