Search code examples
arraysexcelvbafind

Excel Find and compare values over multiple columns if condition met

I have a table with rows representing clients and, each month, i add a column on each row representing the type of orders they make (A, B or C).

I need to identify every client who made an order of type A (in any month) and then, in one of the following months, made a type C order.

However, IF after going from "A" to a "C" type order (one or more months doing a "C"), the client returns to an "A", i don't need to identify him.

"B" orders are indifferent after finding an "A" order

Example given:

Client  M1  M2  M3  M4  Identify
  01    A   B   C   C     Yes
  02    A   B   C   A      No
  03    A   C   B   A      No
  04    B   B   A   C     Yes
  05    B   A   C   B     Yes
  06    C   A   B   B      No
  07    A   A   A   C     Yes
  08    A   A   A   A      No
  09    A   C   A   C     Yes
  10    A   C   C   A      No

Etc.

I don't know if i can do it with a simple formula or using VBA.

EDITED: Since every month i add a new column, formula or VBA should consider all possibilities (like "ACCA", "ACCCCA", and so on). Also added clients 07, 08, 09 and 10 as an example.

Thank you in advance.

JT

Solution

  • without VBA an easy way would be: (in F2 and then copy down)

    =IF(ISERROR(FIND("AC",SUBSTITUTE(B2&C2&D2&E2,"B",""))),"No",IF(ISERROR(FIND("ACA",SUBSTITUTE(B2&C2&D2&E2,"B",""))),"Yes","No"))

    As an "OnlyDoesWhatIAskedFor"-UDF put this in module:

    Public Function getOrder(rng As Range) As String
  Dim runner As Variant, i As Long, comb As Variant
  comb = Array("A", "C", "A")
  getOrder = "No"
  For Each runner In rng.Value
    If runner = comb(i) Then
      If i = 2 Then getOrder = "No": Exit Function
      If i = 1 Then getOrder = "Yes"
      i = i + 1
    ElseIf runner = comb(0) Then
      i = 1
    ElseIf runner <> "B" Then
      i = 0
    End If
  Next
End Function

    then in the cell to output (asuming F2 and simply copy down)

    =getOrder(B2:E2)

    it just checks for the A-C order in the range while B will be ignored and A-C-A also outputs "No"...

    EDIT

    If only the last AC/ACA count then you would need something like this: (in Module)

    Public Function getResult(rng As Range) As String
  Dim a As Variant, b As String
  'Col W - changes the cells to one long string / "B" will be left out
  For Each a In rng.Value
    If a <> "B" Then b = b & a
  Next
  'Col X - skips xxACAxx to Axx till no ACA is left
  While InStr(b, "ACA")
    b = Mid(b, InStr(b, "ACA") + 2)
  Wend
  '<~~~ add from here for ACCA = "No" (ACCAC will still be "YES")
  While InStr(b, "ACCA")
    b = Mid(b, InStr(b, "ACCA") + 2)
  Wend
  '<~~~ add till here for ACCA = "No" (ACCAC will still be "YES")
  'Col Y - Checks if an AC is found in the leftover
  'Col Z - if the check is >0 then it will be YES else No
  If InStr(b, "AC") Then getResult = "Yes" Else getResult = "No"
End Function

    You can see each step it does in the following picture: enter image description here