Tricky Process, requires some smart thinking. Check duplicates

Help Studio
, ,
1

export.XLSX (19.3 KB)

check this excel.
The aim is to check if an document number is found under two accounts. Account is stated on the yellow lines.
How do you think is the best approach?
Read first all account numbers in an array and then add a new column where we add the account number for each row and then we detect duplicates based on two columns, document number and account number?

image
image1892×804 96.8 KB

Hm…please advice, i am sure you are smarter than me to find a better solution.
Thank you so much guys

2

you can think about two strategies for preperations:

  • filling up A col / hlper col with Account info
    OR
  • calculate block index and slice the blocks
1 Like
3

How can I add another column and fill it with the account number for each row?
Thank you Peter

4

filter out the '@5C.... rows
and have a look on the FillUp_BlockBlanks.xaml

When account 200004 is refering to the above rows: 2-13 from excel then we need to do some modification on the filling up. For this modification one of my colleagues @kumar.varun2 can help you

1 Like
5

Hi @hurmet.noka

You can try this approach to add the new column and fill it with Account Number

TrickyProcess.xaml (7.7 KB)

2 Likes
6

I get RowIndex is not defined, should i add an assign activity and RowIndex assign the value:
dt_data.Rows.IndexOf(Row)

7

Hi @hurmet.noka

RowIndex is the variable which contains the index of row in iteration. It is defined in the properties of For Each Row in Data Table Activity.

image

1 Like
8

image
image1818×1157 154 KB

export.XLSX (166.1 KB)

Thank you Kumar

9

After the reverse datatable we have this as the first row

image
image780×68 2.59 KB

Which does not start with Account, hence it tries to match RowIndex - 1, which means its -1.
image
image1488×168 13.4 KB

10

Hi @hurmet.noka

Yes, The input file you shared yesterday was not having such scenario. The last row of the new input file is having the empty first column. I think it contains the total.

We can try by removing it.

In the assign before the for each, just change the right part.

dt_openitems = dt_openitems.AsEnumerable.Reverse.Skip(1).CopyToDataTable
1 Like
11

Disregard last messages, I deleted the last row and then did the reverse. Thank you

12

@hurmet.noka

If you encounter such scenario frequently, then we can modify the approach accordingly.

1 Like
13

Thank you. You and Peter have helped me a lot, I marked you as solution.
1st part of the process is done.

Now we have this output file:
Output.xlsx (25.1 KB)

The aim is to Find Which Documents have different account number.
What are your thoughts on that, or should I open a new post in the forum
Thank you

14

@hurmet.noka

Do you want to know if one document number is having more than one account number?
Please confirm

15

I want to know if the same document number appears on two different accounts.
Ideally we will have a column with Yes No values for each document number.

16

@hurmet.noka

Try this

(
    From row in dt_Output
    Group row by k=row("Document Number")
    into grp=Group
    Where (grp.Count > 1 And grp.Select(Function(gr) gr("Account Number")).Distinct.Count > 1)
    Select k
).ToArray

This gives an array of object, which contains the document number which satisfy your requirements.

17

It gives me an empty array…

18

@hurmet.noka

There are no document numbers which appear on two different accounts

19

I literally changes the excel so that there are duplicates :smiley: But still an empty array
Output.xlsx (8.0 KB)

20

@hurmet.noka

A slight modification

(
    From row In dt_Output
    Group row By k=row("Document Number").ToString.Trim
    Into grp=Group
    Where (grp.Count > 1 And grp.Select(Function(gr) gr("Account Number").ToString.Trim).Distinct.Count > 1)
    Select k
).ToArray
1 Like