I am trying to sort my excel file. I need to sort 2 columns in ascending order.
In the “Sort Data Table” activity i am specifing the “Name” of the column. The problem is it cant find the column name. the specific column name is the “C7” position. When I manually remove the first 6 rows and the column is in the “A7” position the sorting works, why is this?
When i tried to sort with the index position instead it works but i get data that i dont need in the other 6 rows.
the first question is part of this one. I have exported excel file(same as in 1.) I have 2 columns that i want to compare. In Column “H” and column “I” i have data that i need to use.
Column “H” contains names and column “I” contains the word “Yes”.
Is it possible that I can in the same excel file match the unique(the same name can appear multiply times in the same column) names with the word “Yes”. After i need to divide number of “Yes” with the unique name.
Example: The name “David” appears 150 times and “Yes” appears 120 times in the opposing column.
I then want to calculate the difference 120/150=…
Is this possible, I hope you can understand what I mean.
@atomic I feel it would be more helpful if you were to Show your Excel Files / Sample Excel Files of the same format, Then Use Screenhots to Show the Expected Output you need. It Would be faster to Understand and to Solve.
@atomic Then I guess When you use Read Range Activity to read the Excel File as Datatable , use the Range as “A7”, then your First Question might get Solved
@atomic When you use A7 as the range, the First 6 Rows Shouldn’t get included in the Data at all. Can you Confirm if the Datatable Contains the First 6 row Data after Read Range by using the Output Datatable activity and Message Box. The First 6 rows Data Shouldn’t be in the Picture at all
Only problem is that the first two rows in the “tid/time” column doesnt appear logical. instead its a number like 43957,44 when it should be 10:30:12. Its like the Write range activity does it in a writes the data in a weird name, because i also see font and colors of the columns isnt the same as in the new excel file that was created.
@atomic If you want the Format to be the same as that of the Input File, I would Suggest you to Create a Template File of the same Format. You can do this By making a Copy of the Input File, And in the Copied file, delete the First 6 Rows and then Excluding the headers delete all the rows. and Save it in a Different Name. Then After your Operation Use Write Range with Output File name as that of the Template Created ,without Add Headers and Specify Range as “A2”.
@supermanPunch Do you think that my second question is possibly. Because if I cant do it my first question can be done in a different way.
I would maybe be better to remove duplicates and then extract every “individual” in column “H”
and process them as a separate transaction. Because i can get an excel file generated for each individual
and then it is already sorted. Im thinking about if my second question is possibly then that is maybe faster.
@atomic I haven’t understood the 2nd Question completely, i was gonna ask you to provide an Expected Output File after the Operation that you have mentioned is performed, but since the Sorting was not done, I was Sticking on to the 1st Question
In “H” there are names that can appear several times.
In “I” the word “yes” is displayed or it is empty.
The data is dynamic and is different from week to week. Is it possible to match the name extract the same name(David) and determine how many times “Ja” was used?
In this case below David appeard 24 times and Yes 21 times. And later I wish to divide 21/24
