VBA to count multiple record value to one value in single record

[ashokbioinfo]

hi all,


I want to count the below records to two single records (based on cts and ibm). Each string should repeated only once


finance/economy/boy/cts=12


finance/economy/girl/cts=3


finance/economy/boy/cts=6


education/economy/boy/cts=2


commerce/non-economy/girl/cts=3


finance/economy/boy/cts=6


finance/economy/girl/ibm=2


finance/economy/boy/ibm=4


Output should be like this

"education/finance/commerce/economy/non-economy/boy/girl

/cts=32"


"finance/economy/boy/girl/ibm=6"


Please note that data string are frequently changed at respective to the left and right of forward slash and record are in dynamic range.

 

[NARAYANK991]

Hi Ashok ,


The examples you have given all have 4 parts in each string. Will this be true of all records , or can some records have a lesser or greater number of parts ?


Narayan

 

[ashokbioinfo]

thanks narayan for looking the matter.

Yes, every line consists of 4 parts.If string on any one of the part is duplicated, then we need to take only one.

 

[Debraj]

Hi Ashok,


Please try the below code in VBA

______

[pre]

Function ashokbioinfo(BaseString As String, SourceRange As Range) As String
Dim BaseCnt As Long, AnsString As String
BaseCnt = 0
For Each cell In SourceRange
If InStr(1, cell.Text, BaseString) > 1 Then
BaseCnt = BaseCnt + Val(Right(cell.Value, Len(cell.Value) - InStr(1, cell.Value, "=")))
arrstring = Split(cell.Text, "/")
For i = 0 To UBound(arrstring) - 1
If InStr(1, AnsString, arrstring(i)) = 0 Then
AnsString = arrstring(i) & "/" & AnsString
End If
Next i
End If
Next cell
ashokbioinfo = AnsString & BaseString & "=" & BaseCnt
End Function
____

[/pre]
In any cell use formula as


=ashokbioinfo("ibm",A1:A8)

or

=ashokbioinfo("cts",A1:A8)


where A1:A8 is the range for calculation..


Please let me know if you need the same in EXCEL file or you need the answer in a particular sequence..