I apologise if I’m in the wrong place. I have an excel file that contains survey results. A quick run down on the survey:
person name, satisfactory level
There are multiple people in the survey, and I’m trying to output another excel file that has their average score. There are multiple rows of the same person (many people giving feedback for 1 person)
I’ve already gotten the unique names from the excel sheet to a Data table. The count of responses per row is not consistent. And the point model is 1 to 5. I don’t know how to calculate this part.
I see, but how do I check that the calculation only affects the unique names? As in, all the names are in 1 sheet. But I want the output to be:
Bob, 3.25
Alice, 5
Smith, 4.33
I’m trying to use a for each table on every row of the sheet, but then I don’t know how to check the row value against the list of names to extract the correct values for that person’s name
dtResponses.AsEnumerable.Select(Function (x) x(0).toString).Distinct.toArray
is returning an array with the distinct Names
As an alternate we can implement it with
DatatableVar.DefaultView.ToTable(True,“NameColumnName”)
filter datatable is filtering to all rows of the name from current loop
N2 fromats the number into a 2 decimal format e.g. 3.5 to 3.50
I’m sorry for being really dense. I initially opened this thread because I wanted some guidance with the survey RPA I was working on. Except even with your help earlier, I’m still really confused because I have no idea how to implement it in my actual RPA.
It’s the same idea, except a bit more complex. Other than sorting out the individuals, the spreadsheet actually looks a bit different, most columns are ignored. And there are other conditions to sort the data.
Here’s a spreadsheet of the dummy data I am working with. dummy_data.xlsx (23.0 KB)
I tried using my own methods of filtering just for the total scores of specific people (although with many for each loops, which is probably why it took up to 2 hours to process that) and there’s 6 questions instead (the extra comments responders can add are not included)
Here are the conditions:
Per TSO - Average of all questions - per location
Per TSO - Average of all the questions - per module/class
Per RO (Reporting Officer) - Average of all the questions per location for all TSOs under the RO
Per RO - Average of all the questions per module for all TSOs under the RO
I apologise for being difficult. And it would be nice if you could link guides or pages on the different methods you used (especially the ones that involve code!) on how to do this. My original datasheet has 2000 lines and I’d like it to not take too long to process
my implementation from above was simulating a groupby with the help of distincts (making keys) and filtering (making groups). With the second for each the aggregation was simulated. There are also functions for this available: https://www.tutorialsteacher.com/linq/linq-aggregation-operator-average , sum method…
taking the location additional into place it looks to me like subgrouping on a group. On this I will have look on soon share an example with you. But in the meanwhile just enter the topic by the links and the xaml
Thank you! I’ll look into it. I’m actually an intern on their last 3 weeks here and so far they’ve mostly been giving me easy tasks to learn with, except right before the end of my time there they dropped quite the bomb… aha… I’ll come back with any questions when I look at it during work tomorrow! And thank you for guiding me, I’ll do my best to learn!