Excel_Values manipulation

Help
1

Hello All,
I have an excel file full of different values , i have to search for similar values and put them in a consecutive way, .
if i have this input for example
image
I need to get this result
image .
Any Ideas Please!

2

@teyssir

Buddy…!, please find the below steps that can resolve your issue.

  1. Read the table and convert to datatable

  2. Use for each row to loop through the datatable

  3. Use assign activity like
    — ListName = row(0).tostring
    where listname is the list type of string with default value new list(of string) and row is the for each row loop datarow value of datatable

  4. Next use two for each loop one inside the other to iterate through each element of the list. Here comes the comparison of two similar value and placing them consecutively.like this
    for (i=0;i<=item1.count;i++)
    {
    for (j=i+1;j<=item2.count;j++)
    {
    if (i==j)
    {
    //assign that " i " value to variable1
    // use write cell or write range activities to enter that to an excel
    }
    else
    {
    //assign that " i " value to variable 2
    // use write cell or write range activities to enter that to an excel
    }
    } //end second for loop
    }//end first for loop

  5. Thats alll…you can get the result.

Hope this would help you

Cheers…!

1 Like
3

HI @teyssir

So, you need all the similar values close to each other one below the other. Just like a sorted list right?

Follow the below steps.

  1. Use a Read Range activity to read the data in your excel file into a datatable variable
  2. Use a Sort Data Table activity and sort the data in ascending or descending order. This will bring all the similar values close to each other just like what you have in your screenshot.

image
In here, under input provide your datatable which holds the excel data
Under output mention the same datatable so that it will add the sorted data to the same datatable
Under Index give the column index in your excel starting from 0. - Use this if you do not have a column name in excel. If you have a column header, then provide the column name under Name

Then finally use a Write Range activity to write the data back to an excel file…

This should work for you.

Let know whether this works for you to get the problem resolved :slight_smile:

3 Likes