Collate excel survey responses and average them

Hi!

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)

So for example:
Bob, 1 - strongly disagree
Bob, 5 - strongly agree
Bob, 4 - agree
Bob, 3 - neutral
Alice, 5 - strongly agree
Alice, 5 - strongly agree
Smith, 4 - agree
Smith, 4 - agree
Smith, 5 - strongly agree

I’m trying to make the RPA work for any kind of survey response (number of rows are flexible and number of people and their names).

How would I go about this? I’ve already made a sequence that filters out all their names to 1 data table containing all the unique names.

@kartistenn
getting the disctinct names can be done with following:

assign activity:
leftside: Names (DataType: String Array - String())
right side:
YourDataTableVar.AsEnumerable.Select(Function (x) x(NameColIndexOrName).toString).Distinct.toArray

so with a for each over the Names it can be filterered to the different Names.

It is just a starter help and we will support you on the next steps

1 Like

Hi,

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.

Thank you!

strongly disagree = 0
diasgree = 1
neutral = 2
agree = 3
strongly agree = 4

e.g. you define a dictionary (of String, Int32)
so you can use it for a look to retrieve the int value and can use it for calculation

Hi,

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

can you set up a xaml with a build data datable and the sample values. I will have a look on it later after my dinner.

otherwise do it like this:
for each: loop over the names

  • filter datatable for the name of current loop
  • for each row of the filter result for a name
    • sum up the ratings
  • sum / filterereddatatable.Rows.Count
  • add Name and AVG to a second datatable with the statistic info

Hi,

Thank you! That makes sense. I’ve just finished zipping the files for upload here.
Please help me! thank you!

help.zip (8.9 KB)

@kartistenn
have a look here:
help.xaml (14.0 KB)

I just balanced it between essential actvities and some short cuts (RegEx, Distinct)

Let us know your feedback

Hi!

Thank you! I have a few questions though:

  1. What does ‘dtResponses.AsEnumerable.Select(Function (x) x(0).toString).Distinct.toArray’ do?
  2. What does the filter do?
  3. Why is there “N2” behind ‘(tmpCount / dtFiltered.Rows.Count).toString(“N2”)’?

I’m a complete beginner in programming in general, thank you :slight_smile:

Hi, another question I have is that Data tables cannot be converted to enumerables in my Uipath, how were you able to do it in yours?

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

thank you!

@ppr hi! sorry for the random reply again, I have an error when I try to use regex. It says it’s not declared. Thank you!

import. System.Text.Regularexpressions tab import clos to the variable tabgrafik

1 Like

Thank you!

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

@kartistenn
I would suggest to train some more building blocks before jumping in for a begin too complex topics.

there is the groupby concept, which you can explore at first.
groupby: https://www.tutorialsteacher.com/linq/linq-grouping-operator-groupby-tolookup

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…

Have an initial demo here for introduction:
GroupBy_Sum_1Col.xaml (7.9 KB)

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

Happy learning Happy automation

1 Like

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!

1 Like

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