Matching specific rows in a column with specific rows in another column

Hi,

@ppr

I am thinking if it is possible that in the same excel file(sample below): filter the “Utförare” column and then match “JA” column and then perform a calculation if the “JA” is more than 80% compared to the total count of the same “Person” under “Utförare”.

Currently I am doing it like this: I download a file that looks kinda like that one below. Then I sort the “Utförare” and remove the duplicates, after I handle each utförare as a transaction. I then download a file specific to the “Utförare” and I count all the rows and then count all the “JA”. This works for me but i wonder if i can achieve this in the same file with all the “persons” under “Utförare”. I feel like it is possible based at what you did with the other task above.


I Also wonder if its possible to ad the “Utförare” to the task above and then send each “FALSE” to a different excel file: With the columns, “Datum”, “Tid”, “Längd”, “FreeTime”, “TwoMinSlot” and also add the “Utförare” that is correct for the row. Also is it possible to make the “True” and “False” display something else then just “True” and “False” in an Excel-file, example if it is “False” it should instead write “Not Correct”.

Sorry for all my questions :slight_smile:

This is a “sample”:

@atomic
Can you please help me on following:

your question is independed to the other topic on which worked on (Timelengt, 2MinSlot Calculation) and you are asking on some filterings right?

I have doubts If I did get all requirements. So can you reformulate in a short way like:

File X with the structure or reference to screenshot
Actions:

  • Filter
  • Count
  • Add
  • Split…

With a less prosa style we can have more clearness. However I do feel that we will manage and suceed. Thanks

The one that you solved is based on an excel file that looks almost(I cant have some information because of obvious reasons) like this: UipathForumTest.xlsx (10.6 KB)

One of the tasks is to match “Utförare” with “JA” but each unique “Person” under “utförare”

I am doing it like this:

  1. Removing duplicates from from the Excel document.
  2. Filter the Data table so i only output “Utförare”
  3. Now i can use each of these names(Persons) for the next step.
  4. I Get a new Excel file it looks like the one above but only with the current “person”
  5. I Filter the Data Table so I only Output “Utförare” and “Plats”
  6. Now I count the total rows in the excel file like this: DT_sorted.Rows.Count
  7. Then I am interested in how many “Ja” DT_sorted.Select("[reg. på plats] = 'Ja' ").CopyToDataTable.Rows.count gives me this.
  8. The last step is to take totalJA/totalRows * 100.

The thing is I wonder if it is possible to do all this within the original Excel file or if it is to much info to process. Should I do it like I described(1-8)?

Then I would like to send create a new excel file with the result for every person under “Utförare”.

The other task is the one you did before but the information I gave you was first filtered. Maybe it is possible to do everything in the same file.

Maybe it is easier to do it seperate or what do you think?

@atomic
I will go through all information after my regular job is done and will revert back to you

1 Like

It feels a little bad that you will do it, it is much work.

But I will learn based on the things you do :slight_smile:

@atomic
let us do one thing, for being faster

This is the input:

Before processing it has to be filtered on Plats = Ja

so just provide the expected result after processing (groupwise by person, day, length calculation, 2MinSlot Check) just for a single person (less work for you instead of doing it for all persons). I do feel this will clear a lot.

Thanks

Do you mean that i should provide you with the expected result?

could help but only for one person, so i can refer on the target col structure and can derive the tasks.
If 2 sample rows are making all clear, so its also ok for me

I would want that the outcome(Into a new excel file “Build Data Table”) is something like this:

Column “G” I described above, the total amount off an unique person under “utförare”(same Person can appear several times) should be matched to “Ja” under the column “Plats”. It must be devided by totalJA/totalRows(all the rows that is the same unique person) * 100 to get in %.

I dont know if it is to hard to do everything within the same excel file.

Does the picture above help?

I will start this evening and then we bring it to end maybe in some iterations. Ok Thanks

1 Like

Good luck and thank you for wanting to help out. I am currently trying to do it in my way :slight_smile:

I dont know if the last message from me was formulated strange, I still really want to see how you would solve the task. If you have the time or want to :slight_smile:

too noisy requirements
May I ask you to

  • reformulate the requirements or do some visual graphics. or provide a expected output excel
  • dont mix up implementation with requirement (e.g. filtering for ratio counts) just describe which ratio total / ja rwos are needed and how is the formula to calculate it
  • there was no duplicated data found
  • if groups are to build, specify what are the group keys

Thanks, we will manage your case

1 Like

With the excelfile above I need to.

  1. Find out if the “Utförare” have more than 80% “Ja”

    • Its simple to do if there is only one person to compare to the “Ja”
    • But I am wondering if its possible to do the same thing with the Excel file above, there is many persons under utförare.
  2. See if it is possible to do the thing you solved couple of days ago in the excel file above.

I think we must have two excelfiles with the output results. The “Ja” Task I think I can manage. We can focus on the one from before(that you solved). So we only need to focus on the information below.

The excel file looks something like this: UiPathExampleTest2.xlsx (10.9 KB)
This time there are one more Person it could be several.

I want the output in the new excel file to be like this:
image
I only want the “Falskt” to appear in this file along with the other columns and the information.

@atomic
please do not mix, implementation, requirements.
I really do feel that these ping pongs are taking more time as the implementation itself.

So lets come to requirement.

Input is this refered as dtData

Requirement 1: Plats Value %
Platsvalue (as Percentage) is calculated by CountOfUtförare / dtData.Count * 100
CountOfUtförare is defined as: the count of Ja for following group

  • Datum, Utförare
    dtdataCount = All rows (without any filterings)

Example:
dtData.Count = 36
CountOfUtförare for Group 2020-04-15, Person3 has 1 Entry
% = 30,55

So mayb I am wrong then correct. But we do need this precision on requirements for getting the needed quality.

Please think in this direction and share the other requirements with us. Thanks

BTW. 1 Question: are you located in Sweden?

1 Like

I am sorry.

Yes im from sweden, I think it is to hard to explain this over the internet.

I was saying before that we can forget about the task with the “Ja” and “%”. I was wondering more about making the other solution with the “freetime” more advanced based on my previous comment above. In the one that you solved before was perfect but i then thinked about if it is doable to do all the steps within the excel file(UipathExampleTest2.xlsx) above. The one that got solved only handles one utförare.

This one I would like to try make more advanced based on UipathExampleTest2.xlsx
In UipathExampleTest2.xlsx there are mixed dates and persons.