topic Re: Count Repeated/Unique Strings/Values in VBA Forum

Count Repeated/Unique Strings/Values

jtm2020hyo — Thu, 30 Dec 2021 15:41:04 GMT

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?

Re: Count Repeated/Unique Strings/Values

grobnik — Thu, 30 Dec 2021 15:50:01 GMT

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.

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Thu, 30 Dec 2021 16:01:19 GMT

@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?

Re: Count Repeated/Unique Strings/Values

grobnik — Thu, 30 Dec 2021 19:31:40 GMT

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.

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Thu, 30 Dec 2021 21:58:21 GMT

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?

Re: Count Repeated/Unique Strings/Values

grobnik — Fri, 31 Dec 2021 06:21:27 GMT

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

Re: Count Repeated/Unique Strings/Values

grobnik — Fri, 31 Dec 2021 10:11:42 GMT

@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.

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Fri, 31 Dec 2021 23:09:54 GMT

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.

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Sat, 01 Jan 2022 02:01:09 GMT

Happy New Year Mr. @grobnik.

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

Re: Count Repeated/Unique Strings/Values

grobnik — Sat, 01 Jan 2022 02:09:24 GMT

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.

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Sun, 02 Jan 2022 13:22:31 GMT

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)

Re: Count Repeated/Unique Strings/Values

grobnik — Sun, 02 Jan 2022 14:51:01 GMT

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

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Sun, 02 Jan 2022 15:00:21 GMT

Awesome as always.

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

Re: Count Repeated/Unique Strings/Values

grobnik — Sun, 02 Jan 2022 15:12:26 GMT

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

Re: Count Repeated/Unique Strings/Values

grobnik — Mon, 03 Jan 2022 20:19:44 GMT

@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

Re: Count Repeated/Unique Strings/Values

jtm2020hyo — Tue, 04 Jan 2022 10:19:32 GMT

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