• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

comparing two cells that are not number values

S

ssj011

New Member
Hi,

I want to a formula to compair 2 cells and give the difference value eg.
Eg1:

A2 = SSJ011;PBA077;PSD007
B2 = SSJ011;PSD007

then C2 Should give the value as PBA077

Eg1:
A2 = SSJ011;PBA077;PSD007;AKN010;PFA011
B2 = SSJ011;PSD007

Then C2 should give me the value as AKN010;PFA011;PBA077

Is it possible in formula,

Regards
Sachin
 
Hi Sachin,

Concatenation of variable no. of strings along with a deliminator is not possible through Excel inbuilt function.

We have to design a UDF for this.

Are you OK with that?

regards,
 
Hi Narayan,

Thanks for your help.

But the formula is not giving the correct output.

i attached herewith file for your reference, check the my requirement in column "G".

Regards
Sachin
 

Attachments

Function MatchWord(str1 As String, str2 As String) As String
Dim vArr1
Dim vArr2
Dim vTest
Dim lngCnt As Long
vArr1 = Split(Replace(str1, " ", vbNullString), ";")
vArr2 = Split(Replace(str2, " ", vbNullString), ";")
On Error GoTo strExit

For lngCnt = LBound(vArr1) To UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
If IsError(vTest) Then MatchWord = MatchWord & vArr1(lngCnt) & "; "
Next lngCnt
If Len(MatchWord) > 0 Then
MatchWord = Left$(MatchWord, Len(MatchWord) - 2)
Else
strExit:
MatchWord = "No Matches!"
End If

End Function
 
Hi Sachin ,

I deliberately used the word technique in my post ; what is needed to be done is :

1. Have the longer string in column A , and the shorter string in column B

2. Separate the left-most component of the shorter string ; a component is all of the text from position 1 till the first semi-colon ; because we may be left with a string which does not have a semi-colon ( which will happen when we come to the last component ) , we concatenate the string with a semi-colon.

3. Now substitute this component in the longer string with a null string ; essentially we are eliminating this component from the longer string.

Repeat steps 2 and 3 till we have run out of components in the shorter string. What this means is that if there are 3 segments in the shorter string , we have to repeat the above steps thrice.

In the formula I had posted , there were only two components in the shorter string , and hence the steps 2 and 3 had to be repeated only twice.

Narayan
 
Nebu said:
Function MatchWord(str1 As String, str2 As String) As String
Dim vArr1
Dim vArr2
Dim vTest
Dim lngCnt As Long
vArr1 = Split(Replace(str1, " ", vbNullString), ";")
vArr2 = Split(Replace(str2, " ", vbNullString), ";")
On Error GoTo strExit

For lngCnt = LBound(vArr1) To UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
If IsError(vTest) Then MatchWord = MatchWord & vArr1(lngCnt) & "; "
Next lngCnt
If Len(MatchWord) > 0 Then
MatchWord = Left$(MatchWord, Len(MatchWord) - 2)
Else
strExit:
MatchWord = "No Matches!"
End If

End Function
Click to expand...


Thannks alot for this,

Regards
Sachin
 
Nebu said:
Function MatchWord(str1 As String, str2 As String) As String
Dim vArr1
Dim vArr2
Dim vTest
Dim lngCnt As Long
vArr1 = Split(Replace(str1, " ", vbNullString), ";")
vArr2 = Split(Replace(str2, " ", vbNullString), ";")
On Error GoTo strExit

For lngCnt = LBound(vArr1) To UBound(vArr1)
vTest = Application.Match(vArr1(lngCnt), vArr2, 0)
If IsError(vTest) Then MatchWord = MatchWord & vArr1(lngCnt) & "; "
Next lngCnt
If Len(MatchWord) > 0 Then
MatchWord = Left$(MatchWord, Len(MatchWord) - 2)
Else
strExit:
MatchWord = "No Matches!"
End If

End Function
Click to expand...



Hi Nebu,

Please check the column colour with green which is the actual output I need, but the macro function which you had given to me is not giving the correct output as I want.

Regards
Sachin
 

Attachments

You must log in or register to reply here.
Back
Top