How to count instances of a string in data table

Help
1

Hi, I have a data table that’s already in the format as per the first column below.
image
I would now like to count the number of recurrences and append this count to the back of the string (refer to second column above for expected outcome).
How should I go about this?

Thank you!

2

@deliateo We can Convert into this Format, if it is fine by you.
Capture

1 Like
3

@deliateo
find a starter help here
deliateo.xaml (8.9 KB)

2 Likes
4

@ppr

Thanks for the code! It’s working when I run your code, when I try to adapt it to my current working, it is counting the number of lines but not counting the duplicates correctly (ie it’s doing ABC_100_1, ABC_100_2, ADB_1020_3 when it shold be ADB_1020_1).

Could you briefly run through what the following code does so I can better adapt it please?

AsEnumerable.Select(Function (r, index) new Tuple(Of Int32, String)(index, r(0).toString)).Where(Function (t) t.Item1 <=idx And t.Item2.Equals(row(0).toString)).Count

Thank you so much!

1 Like
5

I need all the rows to be preserved though :confused:

1 Like
6

@deliateo
please change the logic that is calculating the count to following:

(From r In dtData.AsEnumerable
Where dtData.Rows.IndexOf( r ) <= idx And r(0).toString.Equals(row(0).toString)
Select r).Count

it is less complex

2 Likes
7

can you share your excel with us? Maybe we have to incorporate a trim logic

8

@ppr

Thanks for the formula! It’s working now. I just had to update the column index to the right one.
Thank you!

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.