comparing multiple character fields

Hi padmathiagarajan,

There are several ways you can do this, probably the simpliest as you only have five columns is just to create a virtual character field with an equation to test all the possibilities.  In your example this is what I came up with for your equation:

@if(COL1 = COL2  .OR. COL1 = COL3 .OR. COL1 = COL4 .OR. COL1 = COL5 .OR. COL2 = COL3 .OR. COL2 = COL4  .OR. COL2 = COL5 .OR. COL3 = COL4 .OR. COL3 = COL5  .OR. COL4 = COL5, "Yes", "No")

If you have more fields or you do this often it might be better to look at custom functions or a script to do the testing.

Hopefully this helps.

Brian

Thanks Brian for such a quick response..  i have two additional questions in the related topic
1. How to do this as a script?
2. If there are two fields which are blank, how to ignore that and check if atleast two fields match. For Eg:

Col 1
Col 2
Col 3
Col 4
Col 5
Exception

A
 
 
A
B
Yes

A
B
 
C
D
No

Basically i would like to check this for non blank fileds to ensure that alteast two columns are not the same.
Thanks in advance
 
Regards
Padma 
 
 

Hi Padma,

Removing the blanks make it more difficult in the equation editor.  It would be possible but the equation would be fairly complex.  This is where using a Custom Function comes in handy.  I have created one that will take five character fields and check for a duplicate and also ignore blanks.  Here is the code and I have attached the Custom Function below.

Once it is installed you can access it by calling #Test_For_Dup_Field in the equation editor.  This is an example of the syntax.

#Test_For_Dup_Field(COL1, COL2, COL3, COL4, COL5)

Option Explicit
Function Test_For_Dup_Field(p1 As String,p2 As String,p3 As String,p4 As String,p5 As String) As String
	Dim fieldArr(4) As String 'use an array to hold the field amounts
	Dim i As Integer
	Dim j As Integer
	
	test_for_dup_field = "No" 'set no as the default in case no matches are found
	'put the contents from the fields in the array and get rid of any leading or trainling spaces
	fieldArr(0) = Trim(p1)
	fieldArr(1) = Trim(p2)
	fieldArr(2) = Trim(p3)
	fieldArr(3) = Trim(p4)
	fieldArr(4) = Trim(p5)
	
	'loops through the first set of arrays
	For i = 0 To 4 
		'loop through the second set of arrays
		For j = (i + 1) To 4
			'ignore blanks
			If fieldArr(i) <> "" And fieldArr(j) <> "" Then
				'if a match is found return Yes and exit the custom function
				If fieldArr(i) = fieldArr(j) Then
					test_for_dup_field = "Yes"
					Exit Function	
				End If		
			End If
		Next j
	Next i

End Function

Thanks a lot Brian. This is what i was exactly looking for. Is there any source from where i can learn how to do looping?