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
How can I add another column and fill it with the account number for each row?
Thank you Peter
filter out the
and have a look on the
when readin int the excel into a datatable (read range) we would expect following result
so we would
filling up the missing GroupX column values
grouping the data on GroupDocs
processing / evaluating the groups
For filling up, have a starter help here:
FillUp_BlockBlanks.xaml (9.3 KB)
Grouping data have alook here:
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
You can try this approach to add the new column and fill it with Account Number
TrickyProcess.xaml (7.7 KB)
I get RowIndex is not defined, should i add an assign activity and RowIndex assign the value:
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.
After the reverse datatable we have this as the first row
Which does not start with
, hence it tries to match RowIndex - 1, which means its -1.
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
Disregard last messages, I deleted the last row and then did the reverse. Thank you
If you encounter such scenario frequently, then we can modify the approach accordingly.
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
Do you want to know if one document number is having more than one account number?
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.
From row in dt_Output
Group row by k=row("Document Number")
Where (grp.Count > 1 And grp.Select(Function(gr) gr("Account Number")).Distinct.Count > 1)
This gives an array of object, which contains the document number which satisfy your requirements.
It gives me an empty array…
There are no document numbers which appear on two different accounts
I literally changes the excel so that there are duplicates
But still an empty array
Output.xlsx (8.0 KB)
A slight modification
From row In dt_Output
Group row By k=row("Document Number").ToString.Trim
Where (grp.Count > 1 And grp.Select(Function(gr) gr("Account Number").ToString.Trim).Distinct.Count > 1)