Remove row 1-6 in excel and one more question

Help Studio
1

I have 2 questions.

  1. 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.

  2. 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.

Thank you.

1 Like
2

@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. :sweat_smile:

1 Like
3

The information is confidential, but i will make up something similar. Give me a few minutes.

2 Likes
4

image
image1062×524 11.1 KB

1 Like
5

@atomic Do you need the First 6 Rows Data ?

1 Like
6

No they just give me a problem as I tried to describe in my first question haha.

1 Like
7

@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

1 Like
8

Done that, it sorts the data on column “C”. but the 6 first rows get added below the columns with weird data.
image

1 Like
9

Maybe its better to remove the first 6 rows and then do the sorting?

1 Like
10

@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 :sweat_smile:

1 Like
11

It works now. Thank you.

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.

1 Like
12

Write range activity does it in a writes the data in a weird name = Write range activity writes the data in a weird way.

Im typing to fast, sorry.

2 Likes
13

@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”.

Let me know if this works.

1 Like
14

@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.

1 Like
15

@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 :sweat_smile:

1 Like
16

Oh okay,

Look at Column “H” and “I”.

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

I also created a sample below:

image
image199×659 2.17 KB

1 Like
17

The same for “Luke”, 7 “Lukes” and 5 “Yes”.
I dont now if it is possible to do this based on the first picture I sent you.

1 Like
18

@atomic So basically you need tto count how many times David appears and how many times Ja appears in their respective Columns right ?

1 Like
19

Exactly and the same for Luke and so on. The excel file is like the first picture i posted.

1 Like
20

@atomic Can you give a Sample File to work on ? :sweat_smile:

1 Like