Counting string values

I have a excel workbook filled with information that I would like to sort and also count but I do not know where to start…
I was thinking that it would work with a for each loop and for each row increment a variable that will in the end print the total amount or something.

What needs to be sorted and counted is this:

What is important is the img files number, which all have a specific word tied to it. For example 1 = GK, 2 = RB, 3 = RW and so on.
Is there any way to create a for each row and if the row contains the number 1, then add +1 to a variable than can in the end be used to print the total value of how many 1.png exist in the excel workbook.

Sorry for the messy explanation

To make it a bit easier(or more difficult) to understand this is what I am trying to accomplish as a result:

@Robert_Wennberg What is LWG and what is 234?

@supermanPunch

Those are just examples of what the final excel workbook would look like. Badly explained by me.

Basically every png represent a text (football position) so 1.png is LWG, 2.png is ST and so on. The value beneath those are where I want to put the actual numbers counted from the first image I attached. So for example, the total of 1.png in the first image COULD be 234…
Is my explanation making a bit more sense now or am I just making it more confusing?

@Robert_Wennberg This type of Data needs an Additional Input File to Map 1.png to LWG and 2.png to ST else it is quite complicated to Achive it :sweat_smile:

@supermanPunch

To try and make things a bit clearer, let’s start over…

I start off by data scraping a web page

where the values I am interested in are how many LWG, RWG, OMF etc there are in total among 20000 something players.
The LWG, RWG, OMF etc however are not text but images, that is why the received data is 1.png, 2.png etc. So I do not necessarily want the images, just the info of how many there are.

Using the scraped data

I want to maybe use a foreach row if that is the easiest, to count how many of the texts there are in total in the excel workbook.
Then I want to take that counted data and paste the total numbers into another workbook in their respective places

And as you can see in the last picture here, every keyword is connected to the number of the png on the web site.

I tried to explain it as clearly as possible, if it is impossible then so be it

@Robert_Wennberg

What about using filter inside for each loop and an assign to count ?

@mz3bel

That sounds like it is on the right track but how would I accomplish that? I have not used filters before and I am pretty stupid when it comes to assigning counts… As you can probably tell…

@Robert_Wennberg You can Accomplish Calculating the Count by Performing GroupBy and Count

@Robert_Wennberg Caan you send that Input Excel, but however the Mapping from 1.png to LWG and so on is quite not possible if no Alternative mapping is provided :sweat_smile:

@Robert_Wennberg

Using Filter Data Table to find the data you are looking for, giving the column, what to keep what to delete than use var = var + 1 or as said before use count to count the rows number.

@supermanPunch

Not quite sure what you mean by mapping, but I will send you the excel!

RawData.xlsx (17.9 KB)

Raw Data.xaml (9.9 KB)

https://we2020.kouryakuki.net/players/

That is all information I can give pretty much

Counting each occurence can be achived by simply using Countif function of Excel.
CalulatedCounts.xlsx (21.0 KB)

1 Like

Would there not be an easier way to just iterate through column B and add +1 to a variable if the row contains 1.png, same thing for 2.png and so on? Or something along those lines.

I will try and see if I can get the Filter Data Table to work! Thank you for the guidance

1 Like

Thank you for teaching me about Excel’s countif function… I had no idea such thing existed

@Robert_Wennberg

The filter data table is very is very to use, let’s say you want to keep all the data that contains “True”, first you choose keep, than you enter the column that you want to check weither the data exists than you choose contains and give the value you want like “True”.

This will give you all the rows that the column A, contains the word “True”.

Than use the counting function.

Thank you for a much more detailed explanation and taking your time to explaining it to me… I am new to all of this so it is a lot to take in! I appreciate everyone taking their time to help though

1 Like

@Robert_Wennberg Check This Workflow, After you open this You may get Missing Activity Error , if you get , then From Manage Packages, Download and Save “Data Manipulations” Package

This is not the Complete Output What you needed though :sweat_smile:

GroupByCount.zip (20.5 KB)

@supermanPunch, I downloaded your workflow and it’s empty.

image

HI @Robert_Wennberg

You can use Datatable select query with wild card you can find count for individuals.