I need your help in excel.

1. I would like to be able to set up a list to map items found in a substring of the payee text field to categories.
   I have row 21 with "verizon" in it, I would like a macro/formula to fill in column H with "cellphone" because it matches the categorization map below.

2. I would like to add as many substrings to category map entries as I want to (make the list longer)

3. I would also like a sheet that sums up the categories for a given date range.

4. I want to check each "payee" row against the map to get the correct category into the category column

5. I would like the categorization map to be in a separate sheet (it's in the same sheet here as an example)

6. The columns are set, you can count on them not moving (category will always be column H and the string you are searching for will always be in column C)

7. I will have thousands of rows in the sheet.

Here is the google sheet

CodePudding user response：

Please, test the next code. It will be very fast even for large ranges, using arrays, working only in memory and dropping the processed result at once. You need to place categorization map in a sheet named "Category Map", in columns "A:B", (headers included). It is counted dynamically, according to the added substrings:

Sub MappingCategories()
   Dim sh As Worksheet, shC As Worksheet, lastR As Long, lastRC As Long
   Dim arrCat, arr, mtch, i As Long, j As Long
   
   Set sh = ActiveSheet 'you may use here the sheet you need
   Set shC = Worksheets("Category Map")
   lastR = sh.Range("C" & sh.rows.count).End(xlUp).row
   lastRC = shC.Range("A" & shC.rows.count).End(xlUp).row
   
   arr = sh.Range("C2:H" & lastR).Value2         'place the range in an array for faster iteration
   arrCat = shC.Range("A2:B" & lastRC).Value2
   
   For i = 1 To UBound(arr)
        For j = 1 To UBound(arrCat)
            If InStr(arr(i, 1), arrCat(j, 1)) > 0 Then arr(i, 6) = arrCat(j, 2): Exit For
        Next j
   Next i
   'dropping the processed array content at once:
   shC.Range("D2").Resize(UBound(arr), UBound(arr, 2)).Value2 = arr
End Sub

About the second requirement, you must firstly fix the column "Posted Date" format and come back with another question showing that the data is consistent and formatted as Date...

CodePudding user response：

With formula:

=IFERROR( 
   INDEX(B:B,
             AGGREGATE(15,6,
                 ROW($B$39:$B$55)/            
                 ISNUMBER(SEARCH($A$39:$A$55,C21)),
             1)),
   "")

Or if you may want to have combined labels (if it has multiple hits it sums them up, comma separated):

=TEXTJOIN(",",1,
     IFERROR(
       INDEX(B:B,
            AGGREGATE(15,6,
                ROW($B$39:$B$55)/          
                ISNUMBER(SEARCH($A$39:$A$55,C21)),
                ROW((A$1:INDEX(A:A,SUM(--ISNUMBER(SEARCH(A$39:A$55,C21)))))))),
     ""))

PS Excel versions prior to Office365 require this to be entered with ctrl shift enter

The summary can be achieved using SUMIFS, but should be a different question if you encounter problems with that. It's also handy to mention your Excel version (for example Office 365, Excel 2019, or prior).