Hi,
i would like to compare multiple character fields in IDEA to check if atleast 2 fields are the same. How do i do this through IDEA script?
Eg:-
Col 1
Col 2
Col 3
Col 4
Col 5
Exception
A
A
B
C
D
Yes
A
B
A
C
D
Yes
A
B
C
D
E
No
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)
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?
Hi Padma,
There are quite a few resources on the web. Just do a google search on VBA for next and you will find lots of information. Below is one link that I found.
https://www.techonthenet.com/excel/formulas/for_next.php
Glad the Custom Function was useful.
Brian