Compare word from column to sentence in Excel

[coolkiran]

Hello everyone

I am stuck with a excel file. I have a excel file which i am downloading from one software. Its basically logs file, i mean which all users logged in and their activity.

What i am trying to do is, i have possible sentence which will not matches exactly but partially it matches. I tried to do it with Find function, but i need to compare it with one column and if match found then i need to put value from next column.

Eg: I have 3 columns, User Type, Activity, and Output.

suppose if activity is "Invoice Created #001", if User type is Admin, then i need "Yes" in Output Column, Sometimes sentence will starts from Some name like "Mike - Invoice Created #002", so this challenge.

Second scenario is , if activity is "Invoice created #991" if User type is Staff, then i need "No", because Staff doesn't have access to create invoice.

So here "Invoice Created" is common, so i have added separately and create column as Admin and marked as "Yes" and for "Staff" marked as "No"

Here what i have done is i have added 3 more columns, like activity which i am updating manually, if i have activities like "Invoice Created #001", "Invoice Created #002" ... etc . here "Invoice Created" is common, so "Invoice Created" i am updating in the column where i have added, in this file its in G Column, and i have 2 roles "Admin" and "Staff", in that column, for Invoice Created, Admin will be Yes and Staff will be No.

Similarly i am updating for all activities.

Now i need to compare "invoice created #001" with "G" Column and "User Type", If User is Admin, then as per "Admin" Column "Yes" Or "Normal Activity", if User is "Staff", then as per "Staff" Column "No" or "Suspicious Activity" should display in C Column,


I have attached sample file.

Can we do it in Excel formulas or Macros. I will be ok with both?

 

[AlanSidman]

Option Explicit

Sub AdminStaff()
    Dim lr As Long, i As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If InStr(Range("B" & i), "Invoice Created") > 0 Then
            If Range("A" & i) = "Admin" Then
                Range("G" & i) = "Invoice Create"
                Range("H" & i) = "Yes"
                Range("I" & i) = "No"
            ElseIf InStr(Range("B" & i), "Invoice Created") > 0 Then
                If Range("A" & i) = "Staff" Then
                    Range("G" & i) = "Logged In"
                    Range("H" & i & ":I" & i) = "No"
                End If
            End If
        End If
            If InStr(Range("B" & i), "Logged") > 0 Then
                    Range("G" & i) = "Logged In"
                    Range("H" & i & ":I" & i) = "No"
            End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

 

[vletm]

coolkiran
Based You ... short sample data and 'Desired Output' ...
For cell C2 =IF(AND(A2="Staff",LEFT(B2,1) ="I"),"Suspecious","Normal") & " Activity"
if You want to take care about 'Mike' then eg
=IF(AND(A2="Staff",IFERROR(FIND("Invoice",B2,1) > 0,FALSE)),"Suspecious","Normal") & " Activity"

 

[vletm]

coolkiran
You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org

• When drafting a question, try and lay out the question in a clear and concise way.
• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
• Don't leave out information more info is better

Questions:
Do Your original writing cover ... above blue text?
How many scenarios did You write?
What for was that 'Desired Output'? = why needs G:H -'table'?
What would be Your 'type's?
Did You write something and want something else ...
or do You know what do You really would like to have?

 

[AlanSidman]

It really helps if you explain your whole story in the thread. We are not mind readers and unless you tell us specifically what you want, then you only get what is specifically asked for, ie. Nothing mentioned about a need for a variable. No mention that highlighted yellow needs to be added. No mention about Ranges B and Logged in, rows 4 & 5. Help us to help you by giving us all the information. Criteria for lookup is in D1.

Option Explicit

Sub AdminStaff()
    Dim lr As Long, i As Long
    Dim crit As Variant
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    crit = Range("D1")
    For i = 2 To lr
        If InStr(Range("B" & i), crit) > 0 Then
            If Range("A" & i) = "Admin" Then
                Range("G" & i) = "Invoice Created"
                Range("H" & i) = "Yes"
                Range("I" & i) = "No"
                Range("C" & i) = "Normal Activity"
            ElseIf InStr(Range("B" & i), crit) > 0 Then
                If Range("A" & i) = "Staff" Then
                    Range("G" & i) = "Logged In"
                    Range("H" & i & ":I" & i) = "No"
                    Range("C" & i) = "Suspicious Activity"
                End If
            End If
        End If
            If InStr(Range("B" & i), "Logged") > 0 Then
                    Range("G" & i) = "Logged In"
                    Range("H" & i & ":I" & i) = "No"
                    Range("C" & i) = "Normal Activity"
            End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

 

[vletm]

coolkiran
You thought and explained something as You wrote,
even those,
You could not give answers to any of my questions about Your thread.
Basic: question > answer.