VBA Different?

[Stephen_IV]

I have a list in Column A of grades separated by a Pipe Character. I need a function in column B that tells me if they are different. Thanks in advance!

F|F
A+|A+
A+|A+
A|A
F|F
A+|A+
A-|B+
F|F
A-|F|A-
F|F
F|F

 

[Rick Rothstein]

Give this formula a try...

=IF(LEFT(A1,FIND("|",A1)-1)=MID(A1,FIND("|",A1)+1,9),"","<<==")

 

[hotabae]

Another possibility:

=IF(LEFT(A1,FIND("|",A1)-1)=RIGHT(A1,LEN(A1)-FIND("|",A1)),"Same","Different")

Edit: oops, didn't see that you have some with 3 grades. My bad...

 

[Fluff]

How about

Function GradeDif(Rng As Range) As Boolean
   Dim i As Long
   GradeDif = True
   For i = 0 To UBound(Split(Rng, "|")) - 1
      If Split(Rng, "|")(i) <> Split(Rng, "|")(i + 1) Then
         GradeDif = False
         Exit For
      End If
   Next i
End Function

& in B1 enter =gradedif(A1) & fill down

 

[Rick Rothstein]

Give this formula a try...

=IF(LEFT(A1,FIND("|",A1)-1)=MID(A1,FIND("|",A1)+1,9),"","<<==")

I missed that some of your values could have 2 (or more) pipe characters. Given that, ignore the above formula and use this one instead...

=IF(LEN(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND("|",A1)-1),""),"|","")),"<<==","")

 

[Stephen_IV]

Yes it would Rick! Thank you everyone who answered this post. Fluff worked perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

I missed that some of your values could have 2 (or more) pipe characters. Given that, ignore the above formula and use this one instead...

=IF(LEN(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,FIND("|",A1)-1),""),"|","")),"<<==","")

I missed that you wanted a VBA solution. We can use the above (modified slightly) in a one-liner UDF (user defined function) as follows...

[table="width: 500"]
[tr]
	[td]Function GradeDif(Rng As Range) As Boolean
  GradeDif = Evaluate(Replace("LEN(SUBSTITUTE(SUBSTITUTE(@,LEFT(@,FIND(""|"",@)-1),""""),""|"",""""))=0", "@", Rng.Address))
End Function[/td]
[/tr]
[/table]

 

[Stephen_IV]

Thanks Rick! Appreciate your guidance also!