Check if substring is found in range of strings

Faustulus: Did you miss the part of the question with these requirements?

I would like to have lists in other columns like
AA                   AB
Food                Car Expenses
FoodstoreA     Shell
Food shopB     Exxon
DeliC               Gulf

You seem to have created a second worksheet, [Lists], instead.

fanpages: I may have misunderstood the question but I programmed what I understood.

I understood that there is a list with expenses, 2 columns, one for description, the other for amount. The task is to separate the amounts into various columns so that all food expenses end up in the column for food expenses, expenses for the car in the column for transport etc. The lists in the second worksheet identify "McDonald" as a food expense but sorts "MacDonald" into "Other (unidentified)" expenses. So, "MacDonald" could be added to the list of key words for food to reduce the need for manual sorting next time around.

OK... but the question does clearly state where the individual category list strings are stored.

fanpages: Yes, indeed. But I took the definition of AA and AB as meaning "somewhere else". Should be better on a separate sheet which can be hidden or even VeryHidden.

I've requested that this question be closed as follows:

Accepted answer: 0 points for IverErling's comment #a39554722
Assisted answer: 250 points for fanpages's comment #a39551617
Assisted answer: 250 points for Faustulus's comment #a39551825

for the following reason:

I did not mark any of you as best solution since they both are great, and very different in the way they approach the problem. THe first one using SQL is very interesting indeed, and the second one splitting the sorting criteria data into a separate sheet and making an open-end solution so easy to expand is also very elegant. I would like to give you both a "Best solution" marker if I could!

Thanks again for truly great help!

IVer

It looks like IVer wants to assign points. Perhaps some one should help him with the procedure.

Haven't I assigned 250 to each of you? The reason I'm in there is because that was the only way I found to not having to select a Best solution, if that is what you're asking.

All the best from
IVer

Yes, you have, and that is perfect!
I had a message in between which indicated your intention while at the same time setting the system on track to award yourself 0 points for solving the problem. I think the administrator also saw that note of yours and fixed it before my request to the same effect came to the top of the pile.
Have a great day!
Faustulus

Dear Faustulus,

I have one question regarding your code. I spent some time with it yesterday and found that regardless of what column I pass to the LastRow function, it still returns the number of rows in the first column (or possibly the column with the most rows in it). E.g. when called from the ExpenseColumn function for column 2, it returns (in my case - my "real" Excel-sheet) 12 rows - which happens to be the number of columns in row 1 of the listsheet. Column 2 has like 5 rows. This results in an error later on when the ExpenseColumn function looks for a matching value in column 2 and comes to row 6 which contains nothing. Nothing matches all values and all data that doesn't have a match in column 1 is put in column 2. I have fixed this with a If check looking for blanks just above the If InStr(1,... statement in ExpenseColumn. Like this:

Private Function ExpenseColumn(LookIn As String) As Long
    
    Dim Ws As Worksheet
    Dim Rl As Long
    Dim R As Long, C As Long
    
    Dim S As String
    
    Set Ws = ThisWorkbook.Worksheets(ListSheet)
    C = 1
    Do While Len(Ws.Cells(1, C).Value)
        Rl = LastRow(C, Ws)
        For R = 1 To Rl
            S = Ws.Cells(R, C).Value
            If S = "" Then GoTo end_of_for '<---------------------------- HERE!
            If InStr(1, LookIn, Ws.Cells(R, C).Value, vbTextCompare) Then
                ExpenseColumn = C
                Exit Function
            End If
end_of_for:
        Next R
        C = C + 1
    Loop
End Function

Select allOpen in new window

It seems to be the

  R = .Cells(.Rows.Count, Col).End(xlUp).Row

Select allOpen in new window

that returns 12 regardless of column.

I wonder if the state of the cells are changed when you enter something in them and then delete it afterwards. I marked the entire region with list data and copied back in a version with only uppercase words until I realised that the InStr function checks and matches regardless of case and then I copied back the original values, so strictly speaking I have copied in empty values in the cells row 6-12 in column 2. Perhaps this is why? Did you understand any of that? ;-)

Erroneous error?

Thx!

IVer

Hi IVer,
I have interated the additional check. It is only necessary if you have rows without contents. Since I don't think that you have such rows it is more likely that the fault you found is related to the other problem. I.e., you only hit blank cells in a column if LastRow isn't set correctly. Anyway, here is the revised code.

Private Function ExpenseColumn(LookIn As String) As Long
    
    Dim Ws As Worksheet
    Dim Rl As Long
    Dim R As Long, C As Long
    
    Dim S As String
    
    Set Ws = ThisWorkbook.Worksheets(ListSheet)
    C = 1
    Do While Len(Ws.Cells(1, C).Value)
        Rl = LastRow(C, Ws)
        For R = 1 To Rl
            S = Ws.Cells(R, C).Value
            If InStr(1, LookIn, Ws.Cells(R, C).Value, vbTextCompare) And _
               Len(S) > 0 Then
                ExpenseColumn = C
                Exit Function
            End If
        Next R
        C = C + 1
    Loop
End Function

Select allOpen in new window

"Best practise" has it that GoTo is a disruptive command, to be avoided. If .. Then .. Else .. End If allows for free flow of the code's logic and is preferred for that reason.

Now, the Function LastRow() is just a function. If you alter the function it will not work as advertised. Therefore, please don't alter it.
The function call is here:-  Rl = LastRow(C, Ws)
C is the parameter which turns into Col in the function.
Of course, we are refering to the list of items being checked for their occurrence in your posting items. So, if your columns there don't have the same length, it is correct that C refers to the column being examined. C is different in each round of the loop - unless you override the parameter by changing something in the function itself.
I didn't quite catch your explanation, but if you inserted blanks in the lists this would cause a problem. Also, if you have cells at the end of the list containing blank spaces LastRow will include them. In principle, the Lists are Admin controlled and should be perfect. Anyway, you are right in looking for the fault there.
If you continue to have a problem with this plese let me take a look at your Lists sheet.

Hi, Faustulus!

I didn't change the LastRow() function, but just debugged it and saw what the different commands returned. That is how I discovered that it returned the same number of rows for column 2 as it did for column 1 regardless of the fact that column 1 had 12 values and column 2 had 5.

I have not deliberately entered empty values, but have copied out and in a bloc of 12x6 rows/columns which consisted of 12 values in col1 and 5 values in col2 plus some values in the other columns. If this action changes the state of the empty cells 6-12 in col2 and the empty cells in cols3-6, that may have caused the strange behavior I saw in LastRow().

Other than that I have now altered / expanded the code to work with my bank statements, and it works really great.

Thanks!
IVer in Oslo

Hi IVer,
Seems that all's well now. I'm glad you got it to work for you.
Regards,
Faustulus

[ http://www.ee-stuff.com/Newsletter-old/102313newsletter.htm ]

IverErling was trying to come up with a spreadsheet that would look for a substring in a range of strings. Both fanpages and Faustulus came up with solutions and sample workbooks that accomplished the same task in different ways, and even gave IverErling an introduction to VBA programming: First, thank you very much for GREAT help! Both your solutions work great and illustrate in a good way that this can be done. Second, this was just a sample to illustrate my "problem". Therefore, Faustulus is correct in assuming that AA and AB meant "somewhere else". I never thought of making a program for it. I have actually programmed quite a lot in VB before, so looking into VBA is definitely an option, and I will study your code and use it as my introduction to VBA. Again, thanks a lot for thorough and quick replies!