Tricky Process, requires some smart thinking. Check duplicates

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?

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

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

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

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

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

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

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

export.XLSX (166.1 KB)

Thank you Kumar

After the reverse datatable we have this as the first row


Which does not start with Account, hence it tries to match RowIndex - 1, which means its -1.

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

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

@hurmet.noka

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

1 Like

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

@hurmet.noka

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

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.

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

It gives me an empty array…

@hurmet.noka

There are no document numbers which appear on two different accounts

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

@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