Group list of strings by continuous alphabetical sequence

[michael.j.gallamo]

I have a data table with a column that are string values, they are tag numbers. The values are alphanumeric e.g. BL6394B. I am trying to retrieve the first and last value of each continuous alphabetical sequence.

Basically create ranges of the values in the column e.g. BL6394B to BL6394D, BL6395L to BL6395L, BL6399V to BL6399Y.

Picture below portrays a sample of the grouping I am trying to accomplish.

The result could be two string values, grouped data tables, a dictionary variable with key and value, anything really that will make the first value of a continuous alphabetical sequence and the last value retrievable for the entire column of tags.

Thanks in advance.

[Yoichi]

Hi,

How about the following?

dict = dt.AsEnumerable().GroupBy(Function(r) r("Tag Number").ToString.SubString(0,6)).ToDictionary(Function(g) g.Key,Function(g) g.ToArray)

Sample
Sample20260107-1.zip (10.8 KB)

Regards,

[Monali_Vekariya (Mona)]

Hi @michael.j.gallamo

You can do this by looping through the tags, comparing the last character, and closing the range when the alphabetical sequence breaks.

Assume the DataTable is already sorted and the column name is “Tag Number”.

Dim result As New List(Of Tuple(Of String, String))

Dim startTag As String = dt.Rows(0)(“Tag Number”).ToString
Dim prevTag As String = startTag

For i As Integer = 1 To dt.Rows.Count - 1
Dim currTag As String = dt.Rows(i)(“Tag Number”).ToString

If Asc(currTag.Last) <> Asc(prevTag.Last) + 1 Then
    result.Add(Tuple.Create(startTag, prevTag))
    startTag = currTag
End If

prevTag = currTag

Next

result.Add(Tuple.Create(startTag, prevTag))

Result will be ranges like:
BL6394B to BL6394D
BL6395L to BL6395L
BL6399V to BL6399Y

You can later convert the result list to a DataTable, Dictionary, or use it directly in the workflow.

[michael.j.gallamo]

Hello,
Thank you very much for the help. This solution has gotten me almost all the way there but I am running into an issue. This seems to only take the final alpha character into consideration for the continuous sequence e.g. the ‘F’ in Bl6397F. I need it to identify the entire sequence. For example the Tags below:
BL6394B
BL6394C
BL6395K
BL6395L
BL6396E
BL6397F
BL6397G
BL6398D
BL6398E
BL6398F
BL6399X
BL6399Y
BL8177W

I am getting the following results
Start Tags — End Tags
BL6394B — BL6394C
BL6395K — BL6395L
*BL6396E — BL6397G
BL6398D — BL6398F
BL6399X — BL6399Y
BL8177W — BL8177W

The issue is with the *starred group. The tags in the original data are alphabetically ordered however have a different numerical value before the last character i.e. ‘6’ vs ‘7’ this should result in an additional different grouping. The ideal output being below.

Start Tags — End Tags
BL6394B — BL6394C
BL6395K — BL6395L
*BL6396E — BL6396E
*BL6397F — BL6397G
BL6398D — BL6398F
BL6399X — BL6399Y
BL8177W — BL8177W

Any help with this additional logic requirement would be helpful.

[Yoichi]

Hi,

I think it’s better to use Grouping as the following sample.

Sample
Sample20260107-1 (2).zip (10.7 KB)

Regards,

[Monali_Vekariya (Mona)]

Hi @michael.j.gallamo

Split each tag into two parts:

1. Prefix = everything except the last character (example: BL6396)
2. Suffix = last character (example: E)

A tag belongs to the same group only if:

• Current prefix = previous prefix
  AND
• Current suffix ASCII value = previous suffix ASCII value + 1

If any one of these conditions fails, start a new group.

Activities / Flow:

• Use For Each to loop through the sorted list of tags

• Inside the loop, use Assign activities:

  • currentPrefix = tag.Substring(0, tag.Length - 1)
  • currentSuffix = tag.Last

• Keep variables for previousPrefix and previousSuffix

• Use an If activity:
  Condition:
  currentPrefix = previousPrefix AndAlso Asc(currentSuffix) = Asc(previousSuffix) + 1

• If True → continue the same group

• If False → close the previous group (set End Tag) and start a new group with the current tag

• After the loop ends, close the final group

This logic will correctly split BL6396E and BL6397F into different groups while still grouping BL6397F and BL6397G together.

[michael.j.gallamo]

Hello,

Thank you for the directions. I have tried this approach and have some varying results. I can provide more detailed information after some more testing. Thank you for the help so far.

[michael.j.gallamo]

Is this logic in addition to the original logic you provided?

If so would the new prefix if activity be placed within the original if statement or is the original logic within this new logic?

Options

• If Asc(currTag.Last) <> Asc(prevTag.Last) + 1 Then
  currentPrefix = previousPrefix AndAlso Asc(currentSuffix) = Asc(previousSuffix) + 1

or

• currentPrefix = previousPrefix AndAlso Asc(currentSuffix) = Asc(previousSuffix) + 1 Then
  If Asc(currTag.Last) <> Asc(prevTag.Last) + 1

[Monali_Vekariya (Mona)]

Replace your original check with this single condition inside the loop:

If currPrefix = prevPrefix AndAlso Asc(currSuffix) = Asc(prevSuffix) + 1 Then
’ continue same group
Else
’ close previous group and start a new group
End If

Where:

• currPrefix = currTag.Substring(0, currTag.Length - 1)
• currSuffix = currTag.Last
• prevPrefix and prevSuffix are from the previous tag

This way, groups only continue if both the prefix is the same and the last character increments alphabetically.