Excel If statement

[Steve2718]

Please can you advise me of a way to determine how I can check a list of documents for certain criteria.

Name Number Title

Joe Smith 12345 51
Joe Smith 12345 51A

Here I would get a correct or something as there is a 51 and 51 A for this record.

Joe Smith 125656 51
Jane Jones 224545 51A

Here I would get an error as there is only a 51 for Joe Smith

Finally if there are 2 51As or 2 51s for a person I need an error message.

I was thinking about IS Number.

Can you help.
Thanks
Steve

 

[Ruddles]

I tend to dive into VBA with very little encouragement, and this struck me as more suited to a VBA solution than using worksheet functions.

How big do these lists tend to get?

 

[Ruddles]

Try this:-

Option Explicit
 
Public Sub MatchReport()
 
  Dim ws As Worksheet
  Dim iLastRow As Long
  Dim arrStore As Variant
  
  Dim i As Long
  Dim j As Long
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
  
  iLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  
  arrStore = ws.Range("A1:C" & CStr(iLastRow))
  For i = 2 To UBound(arrStore, 1) - 1
    For j = i + 1 To UBound(arrStore, 1)
      If arrStore(i, 1) <> "" And arrStore(i, 1) = arrStore(j, 1) And arrStore(i, 2) = arrStore(j, 2) Then
        Select Case arrStore(i, 3) & arrStore(j, 3)
          Case "5151A", "51A51"
            arrStore(i, 1) = "": arrStore(i, 2) = "": arrStore(i, 3) = ""
            arrStore(j, 1) = "": arrStore(j, 2) = "": arrStore(j, 3) = ""
        End Select
      End If
    Next j
  Next i
  
  For i = 2 To UBound(arrStore, 1)
    If arrStore(i, 1) = "" Then
      ws.Cells(i, 4) = "Ok"
    Else
      ws.Cells(i, 4) = "Error"
    End If
  Next i
  
End Sub

This assumes your worksheet is called Sheet1, your data is in columns A-C and column D is free to place a result code.

As always, test on a copy of your workbook.

 

[Steve2718]

Thanks for this but can it be done without using Visual Basic.
Thanks
Steve

 

[Steve2718]

I would be looking at about 500 rows.
Ta
Steve

 

[SuperFerret]

Um... pivot table maybe?

 

[Ruddles]

Thanks for this but can it be done without using Visual Basic.

Possibly, but it took me a few minutes to cobble together the VBA whereas it would have taken me much longer to produce a function-based solution.

I'm sure someone will come up with a non-VBA solution before too long.