Count Repeated/Unique Strings/Values 2

[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?
Tags (0)
Add tags
Report
2 REPLIES
Sort:

 

[Andrzejek]

>How can I count all "C*" repeated values
I know this example is a VBA, but it should be easily converted / translated to VBScipt:

Option Explicit

Sub MyCount()
Dim ArrSource As String
Dim i As Integer
Dim intFound As Integer

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

For i = 1 To Len(ArrSource)
    If Mid(ArrSource, i, 1) = "C" Then
        intFound = intFound + 1
    End If
Next i

MsgBox "Found " & intFound & " C's"

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Sounds like a homework assignment to me.

What's the business case for this requirement?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mikrom]

IMO, it's home exercise on string handling and arrays in Vbscript.
There are several ways how to do it.
I would prefer one 1-dimensional array of strings and one dictionary for counting the strings - like this:
my_array = ["A1","B2","C1","A2","B2","C1",..,"A7","B2","C5"]
my_dictionary = {"A1":1, "B2":7, "C1":2, .., "C2":2, .., "C5":1}

 

[jtm2020hyo]

@SkipVought

In my case, is an electrical plan design feature that I need add.

for example, C1,C2 are wire-types.

... If we have 5 wires of type C1, then I need annotate 5 x C1 (being "5" the number of repetitions of type "C1")

 

[SkipVought]

That's a valid business case.
Glad you got a good solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[jtm2020hyo]

Thanks a lot Mr. @Andrzejek for this solution.

... but is there any alternative solution using Split to separate my String in ";" and a twice Split for ";", and Then comparing all index 2 (all values with C*) to count unique values?

 

[jtm2020hyo]

IMO, it's home exercise on string handling and arrays in Vbscript. There are several ways how to do it. I would prefer one 1-dimensional array of strings and one dictionary for counting the strings - like this: my_array = ["A1","B2","C1","A2","B2","C1",..,"A7","B2","C5"] my_dictionary = {"A1":1, "B2":7, "C1":2, .., "C2":2, .., "C5":1}

Hello Mr. @mikrom, Do you could share any sample? look interesting your proposal.

 

[mikrom]

Do you could share any sample? look interesting your proposal.

Hi jtm2020hyo,

Here is the example:

jtm2020hyo

'set true to see debug information
DBG_INFO = false 

my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5"
wscript.echo("Input: my_str = """ & my_str & """")

'replace all semicolons (eventually followed by space) by commas
set re = new regexp
re.pattern = ";\s*"
re.global = True
my_str = re.replace(my_str, ",")

if DBG_INFO then wscript.echo("*DBG_INFO: my_str = " & my_str)

'split string into array
my_array = split(my_str, ",")

if DBG_INFO then
  'show what is in the array
  wscript.echo("*DBG_INFO: my_array:")
  for i = 0 to ubound(my_array) 
    wscript.echo("  my_array[" & i & "] = " & my_array(i))
  next
end if

'create dictionary where keys are words and values are count of the words  
set my_word_count_dict = CreateObject("Scripting.Dictionary")

'iterate over the array of words and create dictionary of word counts
for each word in my_array
  'add array element to dictionary
  if my_word_count_dict.exists(word) then
    my_word_count_dict.item(word) = my_word_count_dict.item(word) + 1
  else
    my_word_count_dict.item(word) = 1
  end if
next

if DBG_INFO then
    'show what is in the dictionary
    wscript.echo("*DBG_INFO: my_word_count_dict: ")
    for each key in my_word_count_dict.keys
      wscript.echo("  """ & key & """" & " ==> " & my_word_count_dict.item(key))
    next
end if

'get result from dictionary
result = ""
n = ubound(my_word_count_dict.keys) + 1
i = 0
for each word in my_word_count_dict.keys
   i = i + 1
   if left(word, 1) = "C" then
      result = result & my_word_count_dict.item(word) & "," & word
      if i < n then
        result = result & " ;"
      end if
   end if
next
wscript.echo("Result = """ & result & """")

Output:

c:\mikrom\Work\VBscript>cscript /NoLogo jtm2020hyo.vbs
Input: my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5"
Result = "2,C1 ;2,C2 ;1,C3 ;1,C4 ;1,C5"

The code is longer because I included some printouts, so you could better understand how it works.
You can activate it by setting on the beginning of the script:

DBG_INFO = true

 

[jtm2020hyo]

Here is the example:...

Awesome code, works perfectly. I tried edit de code to replace the C1,C2,C3,etc... to use any value but your code is very advanced for me.

I would greatly appreciate your help if help me to edit the code to count and list unique values in the third index after each semicolon (";")?

 

[mikrom]

help me to edit the code to count and list unique values in the third index after each semicolon (";")?

Hi jtm2020hyo,

I really don't understand what you mean with the third index ?
As I understood the task, the input string contains 2-character words which are separated by commas and semicolons.
You could see, that the dictionary my_word_count_dict contains all words from the input string with their counts (the keys in the dictionary are the words and the values of the dictionary are counts of the words).
So for example, if you want as result the words "A*" with their counts, you only have to change in the last loop the if-condition to:

..
   if left(word, 1) = "A" then
      ..
      ..
   end if
..

If you want to have all the words with their counts, then completely remove the if condition from the last loop, so the last loop will be:

for each word in my_word_count_dict.keys
  i = i + 1
  result = result & my_word_count_dict.item(word) & "," & word
  if i < n then
    result = result & " ; "
  end if
next

 

[jtm2020hyo]

I really don't understand what you mean with the third index ?

I mean this:

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

ArrSource1 = Split(ArrSource , ";")

ArrSource2 = Split(ArrSource1(0) , ",")

wscript.echo(ArrSource2(2)) 'result in "C1", here all values with variable(2) are the third index

Also,I think is need solve/fix the code below to create all variables but not sure if possible or if just can be possible create variable manually writing to have all values indexed:

https://stackoverflow.com/questions...cally-name-vbscript-variables-inside-for-loop

'hypothetical
for i = i to some_number
dim "prefix" & i
"prefix" & i = 2 * i
next

 

[mikrom]

Hi jtm2020hyo,

Ok you can do that so as you described too:

1) split ArrSource = "A1,B2,C1;A2,B2,C1;A3,B2,C2;A4,B2,C2;A5,B2,C3;A6,B2,C4;A7,B2,C5"
into array of 7 string elements:
my_array(0) = "A1,B2,C1"
my_array(1) = "A2,B2,C1"
my_array(2) = "A3,B2,C2"
my_array(3) = "A4,B2,C2"
my_array(4) = "A5,B2,C3"
my_array(5) = "A6,B2,C4"
my_array(6) = "A7,B2,C5"

2) iterate over the my_array and split every array element into temporary array of 3 string elements
for the my_array(0) you will get
tmp_array(0) = "A1"
tmp_array(1) = "B2"
tmp_array(2) = "C1"
then you have iterate over tmp_array and count its elements using dictionary
for the my_array(1) you will get
tmp_array(0) = "A2"
tmp_array(1) = "B2"
tmp_array(2) = "C1"
then iterate over tmp_array and count its elements using dictionary
...
and so on
But it's similar as i have done, at the end you will get the dictionary of word counts.

The stackoverflow link you mentioned is about dictionaries too.

Maybe I didn't quite understand what you need.
It would be best if you show what the output should look like.

 

[jtm2020hyo]

Maybe I didn't quite understand what you need.

thanks for the fast reply.

what I was searching is a 2D array like this:

https://forums.autodesk.com/t5/vba/count-repeated-unique-strings-values/m-p/10851492#M105864

here is the VBA code used:

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

... but this is still advanced for me. How could convert this code to VBS and finally count unique values?

 

[mikrom]

Hi jtm2020hyo
The subroutine you posted works in VBscript too:

'set true to see debug information
DBG_INFO = true

my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
wscript.echo("Input: my_str = """ & my_str & """")

set re = CreateObject("vbscript.regexp")
'remove last semicolon
re.pattern = ";\s*$"
my_str = re.replace(my_str, "")

'remove all spaces
re.pattern = "\s+"
re.global = True
my_str = re.replace(my_str, "")


call SplitArrSource(my_str)

'------------------------------------------------------------------------------
Sub SplitArrSource(ArrSource)
CountA = 1
N = 0
Dim ArrX()
Dim ArrA1()
Dim ArrB2()
Dim arrSplitStrings2()
arrSplitStrings1 = Split(ArrSource, ";")
if DBG_INFO then call  print_array(arrSplitStrings1, "arrSplitStrings1")
For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    if DBG_INFO then
      wscript.echo(" X = " & X)
      call  print_array(arrSplitStrings2(N), "arrSplitStrings2(" & N & ")")
    end if
    N = N + 1
Next
End Sub 

sub print_array(my_array, my_array_name)
  for i = LBound(my_array) To UBound(my_array)
    wscript.echo(my_array_name & "(" & i & ") = " & """" & my_array(i) & """")
  next
end sub

output:

Input: my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
arrSplitStrings1(0) = "A1,B2,C1"
arrSplitStrings1(1) = "A2,B2,C1"
arrSplitStrings1(2) = "A3,B2,C2"
arrSplitStrings1(3) = "A4,B2,C2"
arrSplitStrings1(4) = "A5,B2,C3"
arrSplitStrings1(5) = "A6,B2,C4"
arrSplitStrings1(6) = "A7,B2,C5"
 X = 0
arrSplitStrings2(0)(0) = "A1"
arrSplitStrings2(0)(1) = "B2"
arrSplitStrings2(0)(2) = "C1"
 X = 1
arrSplitStrings2(1)(0) = "A2"
arrSplitStrings2(1)(1) = "B2"
arrSplitStrings2(1)(2) = "C1"
 X = 2
arrSplitStrings2(2)(0) = "A3"
arrSplitStrings2(2)(1) = "B2"
arrSplitStrings2(2)(2) = "C2"
 X = 3
arrSplitStrings2(3)(0) = "A4"
arrSplitStrings2(3)(1) = "B2"
arrSplitStrings2(3)(2) = "C2"
 X = 4
arrSplitStrings2(4)(0) = "A5"
arrSplitStrings2(4)(1) = "B2"
arrSplitStrings2(4)(2) = "C3"
 X = 5
arrSplitStrings2(5)(0) = "A6"
arrSplitStrings2(5)(1) = "B2"
arrSplitStrings2(5)(2) = "C4"
 X = 6
arrSplitStrings2(6)(0) = "A7"
arrSplitStrings2(6)(1) = "B2"
arrSplitStrings2(6)(2) = "C5"

However it's only about creating the arrays. Counting is not implemented.

 

[mikrom]

Hi jtm2020hyo,

I added in the subroutine word counting using dictionary and removed all printouts to make code shorter.
So here is the solution with arrays as you proposed:

jtm2020hyo.vbs

my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
wscript.echo("Input: my_str = """ & my_str & """")

set re = CreateObject("vbscript.regexp")
'remove last semicolon
re.pattern = ";\s*$"
my_str = re.replace(my_str, "")

'remove all spaces
re.pattern = "\s+"
re.global = True
my_str = re.replace(my_str, "")


call SplitArrSourceAndCountWords(my_str)

'------------------------------------------------------------------------------
Sub SplitArrSourceAndCountWords(ArrSource)
  'create dictionary where keys are words and values are count of the words  
  set my_word_count_dict = CreateObject("scripting.dictionary")
  CountA = 1
  N = 0
  Dim ArrX()
  Dim ArrA1()
  Dim ArrB2()
  Dim arrSplitStrings2()
  arrSplitStrings1 = Split(ArrSource, ";")
  For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    'iterate over the array of words and create dictionary of word counts
    for each word in arrSplitStrings2(N)
      'add array element to dictionary
      if my_word_count_dict.exists(word) then
        my_word_count_dict.item(word) = my_word_count_dict.item(word) + 1
      else
        my_word_count_dict.item(word) = 1
      end if
    next
    N = N + 1
  Next
   'get result from dictionary
   result = ""
   n = ubound(my_word_count_dict.keys) + 1
   i = 0
   for each word in my_word_count_dict.keys
     i = i + 1
     if left(word, 1) = "C" then
       result = result & my_word_count_dict.item(word) & "," & word
       if i < n then
         result = result & " ; "
       end if
     end if
   next
   wscript.echo("Result = """ & result & """")
End Sub

Output:

c:\mikrom\Work\VBscript>cscript /NoLogo jtm2020hyo.vbs
Input: my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
Result = "2,C1 ; 2,C2 ; 1,C3 ; 1,C4 ; 1,C5"

 

[jtm2020hyo]

Output

Works perfectly, thanks a lot.

... 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(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)

 

[mikrom]

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(2)" in my arrSplitStrings2 array 2D?

For the given input string: "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
the values arrSplitStrings2(N)(2) where N goes from 0 to 6 are:
C1, C1, C2, C2, C3, C4, C5

The values are repeated, you can get them using this:

modification in Sub SplitArrSourceAndCountWords(ArrSource):
-----------------------------------------------------------
  'get result from array
  result = ""
  for i = 0 to N-1
    word = arrSplitStrings2(i)(2)
    result = result & "1" & "," & word
    if i < N-1 then
      result = result & " ; "
    end if
  next
  wscript.echo("Result = """ & result & """")

then the output of the script is:

Input: my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
Result = "1,C1 ; 1,C1 ; 1,C2 ; 1,C2 ; 1,C3 ; 1,C4 ; 1,C5"

 

[mikrom]

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(2)" in my arrSplitStrings2 array 2D?

For unique values we can adapt the code so, that we use as dictionary keys only those elements arrSplitStrings2(N)(2):

Sub SplitArrSourceAndCountWords(ArrSource)
  'create dictionary where keys are words and values are count of the words  
  set my_word_count_dict = CreateObject("scripting.dictionary")
  CountA = 1
  N = 0
  Dim ArrX()
  Dim ArrA1()
  Dim ArrB2()
  Dim arrSplitStrings2()
  arrSplitStrings1 = Split(ArrSource, ";")
  For X = LBound(arrSplitStrings1) To UBound(arrSplitStrings1)
    ReDim Preserve arrSplitStrings2(N)
    arrSplitStrings2(N) = Split(arrSplitStrings1(X), ",")
    'for arrSplitStrings2(N)(2) create dictionary of word counts
    word = arrSplitStrings2(N)(2)
    if my_word_count_dict.exists(word) then
      my_word_count_dict.item(word) = my_word_count_dict.item(word) + 1
    else
      my_word_count_dict.item(word) = 1
    end if
    N = N + 1
  Next
  'get result from dictionary
  result = ""
  n = ubound(my_word_count_dict.keys) + 1
  i = 0
  for each word in my_word_count_dict.keys
    i = i + 1
    result = result & my_word_count_dict.item(word) & "," & word
    if i < n then
      result = result & " ; "
    end if
  next
  wscript.echo("Result = """ & result & """")
End Sub

then the output is:

Input: my_str = "A1,B2,C1; A2,B2,C1; A3,B2,C2; A4,B2,C2;A5,B2,C3; A6,B2,C4; A7,B2,C5;"
Result = "2,C1 ; 2,C2 ; 1,C3 ; 1,C4 ; 1,C5"

 

[jtm2020hyo]

Thank a lot to everyone. I finally solved this

Was necessary modify a bit to be used in AutoCAD MEP, there the software always request finish with a "RESULT =".

Also I received s bit of help here:

https://forums.autodesk.com/t5/vba/...s-values/m-p/10855843/highlight/false#M105880