Count Repeated/Unique Strings/Values

[jtm2020hyo]

I have an Variable like this:

ArrSource = A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;

'Note: ArrSource is String

How can I count all "C*" repeated values, I mean something like this:

1) Convert Arr1 to multiple array:

Arr1 = A1,B2,C1

Arr2 = A2,B2,C1

Arr3 = A3,B2,C2

Arr4 = A4,B2,C2

Arr5 = A5,B2,C3

Arr6 = A6,B2,C4

Arr7 = A7,B2,C5;

2) Then count all repeated value in the index 2 (third column) then store it in a variable in index 0 and their unique/repeated value in index 1

Arr_01 = 2,C1

Arr_02 = 2,C2

Arr_03 = 1,C3

Arr_04 = 1,C4

Arr_05 = 1,C5

3) concatenate such value with  the Unique Value in a Variable like this:

ArrFinal = 2,C1 ; 2,C2 ; 1,C3 ; 1,C4 ; 1,C5

Is there some help?

[grobnik]

Hi,

In my opinion there is a way little bit simple

If I understood well your array is composed by A1,B2,C1 as Item(0) and for example A2,B2,C1 as Item(1) it's correct my understand ?

Second question is the string length it's always the same ?

You can use the Instr function which return the position of a determinate string inside a string, if found the position will be returned by function, and array content could be manipulated by Mid function and counted.

If you can try to share your string I'll try to do some test.

[jtm2020hyo]

---

@grobnik wrote:

If I understood well your array is composed by A1,B2,C1 as Item(0) and for example A2,B2,C1 as Item(1) it's correct my understand ?

---

yes, but the first value start like an entire String, I could use Split function to separate my String to Array, but after that no sure what should I do.

---

@grobnik wrote:

Hi,

In my opinion there is a way little bit simple

Second question is the string length it's always the same ?

---

No, index in position 0,1,2 could change their length, and possibly I could add index position 3,4,5,etc... Maybe another Split Function could help?

---

@grobnik wrote:

You can use the Instr function which return the position of a determinate string inside a string, if found the position will be returned by function, and array content could be manipulated by Mid function and counted.

If you can try to share your string I'll try to do some test.

---

actually I just have a AutoCAD MEP file with VBScript functions , not sure if that could help. but in general the sample shared above It's enough in my opinion.

PD: is there any Function to count repeated/unique values?

[grobnik]

try the below code it's VBA not VBscript, 

Sub TextArray()
Dim ArrSource(10) As String
Dim N As Integer
Dim X As Integer

ArrSource(0) = "A1,B2,C1"
ArrSource(1) = "A2,B2,C1"
ArrSource(2) = "A3,B2,C2"
ArrSource(3) = "A4,B2,C2"
ArrSource(4) = "A5,B2,C3"
ArrSource(5) = "A6,B2,C4"
ArrSource(8) = "A7,B2,C5"

For X = LBound(ArrSource) To UBound(ArrSource)
If ArrSource(X) Like "A*" Then
CountA = CountA + 1
End If
Next X
Debug.Print CountA
End Sub

Result will be 7 because all text in ArrSource which containing A* will be counted.

I don't know if this helps you or not, or probably I don't understood well your issue.

[jtm2020hyo]

thanks, looks cool.

... But is there any method that uses SPLIT commnad to separate elements per ";" and "," and convert it in multiple arrays or nested arrays?

[grobnik]

Hi @jtm2020hyo 

You can use instr function see web site for instruction and example here the link

InStr function (Visual Basic for Applications) | Microsoft Docs

[grobnik · Accepted solution]

@jtm2020hyo 

try this code for split

Sub SplitArrSource()
ArrSource = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
CountA = 1
N = 0
Dim ArrX() As String
Dim ArrA1() As String
Dim ArrB2() As String
Dim arrSplitStrings2() As Variant
arrSplitStrings1 = Split(ArrSource, ";")
For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    N = N + 1
Next
End Sub

Here the result

You have to pay attention to space char if any inside the string

The above code has been developed inside Excel VBA Module, if you are using Autocad VBA you have to reference the Excel function into your Autocad Macro and use Execel Function pointing to Excel Object.

[jtm2020hyo]

this is exactly what I was searching for, thanks a lot Mr. @grobnik , I will need study your 2D array to translate it to VBScript.

[jtm2020hyo]

Happy New Year Mr. @grobnik.

... just curiously, What software are you using to debug your code?

[grobnik]

Hi,
It's embededd inside VBA module. I guess you can find with MsOffice VBA
module.
I guess you can find into Autocad VBA development tools too.
The code in the post it's made by Excel VBA. I don't know MEP but for sure
you can find development tools with VBA.

[jtm2020hyo]

Good day Mr @grobnik 

... but how could I output the string concatenation of the array that contain the unique/repeated value of all index with the shape of "variable(n)(2)" in my arrSplitStrings2 array 2D? something like this:

ArrSource = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"

N = 0

arrSplitStrings1 = Split(ArrSource, ";")
For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    N = N + 1
Next

wscript.echo(arrSplitStrings2(1)(2)) 

the result should should be something like :

arrayfinal = array("2,C1 ; 2,C2 ; 1,C3 ; 1,C4 ; 1,C5")

 ... being arrayfinal  a 2D array of (5x2)

[grobnik]

Hi, @jtm2020hyo 

I added some part of code showing you how to count the total count of A*, B*, C* with "Like" function always available in Excel.

As final result you will have three different variable  with count of A* composed by the total count a comma, and text counted.

FinalStringA = 8,A*
FinalStringB = 11,B*
FinalStringC = 11,C*

Again I don't know how your source string is composed, here an example with A* B* C* search criteria (so for example A1, A2, B1, B2, C1, C2, etc) 

If you want to create an array of final result you can.

I hope the can help you more, but I'm not in your mind, and I don't have the entire string.

For Example I applied the UBound(arrSplitStrings2) - 1 because last char in the source string it's "blank" so the split function create the 7 position of array empty. But I don't know if you will have blank char in your string or source array.

For the same reason (presence of blank char before A or B or C) I used like "*A*" so all string containing any char before A and any char after A will be considered valid for count of type "A", "B" and "C".

Sub SplitArrSource()
ArrSource = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
N = 0
Dim arrSplitStrings2() As Variant
Dim CountA As Integer
Dim CountB As Integer
Dim CountC As Integer

arrSplitStrings1 = Split(ArrSource, ";")
For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    N = N + 1
Next

For XX = LBound(arrSplitStrings2) To UBound(arrSplitStrings2) - 1
    StringA = arrSplitStrings2(XX)
    If StringA(0) Like "*A*" = True Then
        CountA = CountA + 1
        FinalStringA = CStr(CountA) & "," & "A*"
        
    End If
    If StringA(1) Like "*B*" = True Then
        CountB = CountB + 1
        FinalStringB = CStr(CountB) & "," & "B*"
        
    End If
    If StringA(2) Like "*C*" = True Then
        CountC = CountC + 1
        FinalStringC = CStr(CountC) & "," & "C*"
        
    End If
Next XX

Debug.Print FinalStringA
Debug.Print FinalStringB
Debug.Print FinalStringC

End Sub

[jtm2020hyo]

Awesome as always.

Is there any method to extract a column in a for loop?

[grobnik]

I don't understand, I'm very sorry.

I used the above solution because the final string containing A1, B1 etc is composed as I showed before, if you have a different string or source array structure or contents code shall be modified in order to achive your goal.
Share the entire string for example and show us what do you want to extract, I guess looking at the entire string we can help you more, and overall explain what final result do you want.
Bye

[grobnik]

@jtm2020hyo 

Hi, if you look at this post https://forums.autodesk.com/t5/vba/autocad-mep-and-vba/td-p/10852709, seems that AutocadMep could support VBA, so if you develop your soft for string managing or better directly the MEP object by VBA you should solve your issue with data extraction.

Bye

[jtm2020hyo]

Thanks a lot Mr. @grobnik . I already solved this by myself.

I received a bit help here too

https://www.tek-tips.com/viewthread.cfm?qid=1813199