How to I compare excel columns and add remarks?

[tokio_marine]

Hi,

I have three conditions for comparing Excel columns and add remarks.

1. If all the four columns are duplicated, remark as “SKIPPED”.
2. If the column “PV” has duplicates, remark as “DUPLICATE”.
3. If column “NAME” & “AMOUNT” matches, remark as “RECHECK”.

How do I achieve this ?

[Anil_G (Anil Gorthi)]

Hi @tokio_marine

1. Read the data into datatble(dt)
2. Use a for each row in datatable
3. Inside the loop use a filter datatable and filter the dt with all four columns into a newdt…
   If newdt.rowcount>1 then assign ‘currentrow("Remarks")="Skipped"
4. In the else part use another filter datatable and filter dr with only name and amount into a newdt…
   If newdt.rowcount>1 then assign ‘currentrow("Remarks")="recheck"
5. Repeat same for only pv column in the else block of step4

Cheers

[Yoichi]

Hi,

I think it’s necessary to clarify case that meets multiple conditions .
However, for now, can you try the following sample? This returns same result as the above.

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("NAME").ToString,r("AMOUNT").ToString)).Where(Function(g) g.Count>1).SelectMany(Function(g) g).ToList.ForEach(
Sub(r)
    r("REMARKS")="RECHECK"
End Sub
)
dt.AsEnumerable.GroupBy(Function(r) r("PV").ToString).Where(Function(g) g.Count>1).SelectMany(Function(g) g).ToList.ForEach(
Sub(r)
    r("REMARKS")="DUPLICATE"
End Sub
)
dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("NAME").ToString,r("ID").ToString,r("PV").ToString,r("AMOUNT").ToString)).Where(Function(g) g.Count>1).SelectMany(Function(g) g).ToList.ForEach(
Sub(r)
    r("REMARKS")="SKIPPED"
End Sub
)

Sample20221210-5aL.zip (3.0 KB)

Regards,

[hikumar (Kumar)]

Seems this is working nicely, Could please explain the invoke code you used, I am not able to figure it out

[Yoichi]

Hi,

Could please explain the invoke code you used

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("NAME").ToString,r("AMOUNT").ToString)).

This create group which key is set of “Name” and “Amount”.

Where(Function(g) g.Count>1).

Next, filter out set of rows which number of rows is 1. (This means remaining is duplicated.)

SelectMany(Function(g) g).ToList.ForEach(
Sub(r)
    r("REMARKS")="RECHECK"
End Sub
)

Finally, update “RECHECK” in REMARK column for remaining rows.

We can use the above expression for the other conditions with a little modification…

Hope this helps you.

Regards,

[tokio_marine]

(post deleted by author)

[tokio_marine]

Hi Yoichi,

Thanks for responding back.

PFA…

The whole datatable is getting written in the “REMARKS” column.

Since new users cannot attach files, I have uploaded the input Excel file to cat moe and attached the link.

https://files.catbox.moe/m1e6hk.xlsx (EXCEL FILE)

[Yoichi]

HI,

Can you share screenshot of your workflow?
It might be better to use “ExcelApplicationScope” or “UseExcelFile” with Read/WriteRange instaed of Workbook-WriteRange. Can you try it?

Regards,

[tokio_marine]

[Yoichi]

Hi,

Can you try to modify start cell property of WriteRange activity to “A1”?

Regards,

[tokio_marine]

How do I use write cell to add the remarks instead of rewriting the dt again ?

Because I’ll be using the same Excel forever. And initially I take only the rows with blank remarks as my transactiondata. I don’t process the ones with success remarks. So rewriting the dt might disturb the existing data.

Please let me know if you can connect via zoom for better understanding and quick solution.

[Yoichi]

@tokio_marine

How do I use write cell to add the remarks instead of rewriting the dt again ?

How about the following?

Sample20221210-5aLv2.zip (18.3 KB)

Regards,

[tokio_marine]

Thanks a lot Yoichi. It worked.

For the condition “Duplicate”, how do I get the index like in the snap attached ?

[tokio_marine]

Hi Yoichi,

I process only the rows, where the remarks column is blank.

But when trying the method given by you, the cells with “Success” remark becomes blank.

Please help me with this.

I have attached the project file for your reference.

Thank you

FAB.zip (1.8 MB)

[Yoichi]

Hi,

@tokio_marine

I process only the rows, where the remarks column is blank.

If you want to update only blank cell, the following will work.
(Please modify all the similar expression in InvokeCode)

If (String.IsNullOrEmpty(r("REMARKS").ToString)) Then
    r("REMARKS")="RECHECK"
End If

If you need to exclude blank cell in evaluation, we need to review expression.

Regards,

[tokio_marine]

I need to look for duplicates in all the rows including the ones with remarks. But need to add remark only in the rows with blank remarks.

[tokio_marine]

Hi Yoichi,

Thank you for your continuous support.

Please let me know if you can support via zoom.

I’ve been stuck in this part for quite a long. Will be a great help if you extend your support via zoom.

Thank you.

[tokio_marine]

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r(“IBAN Number”).ToString,r(“Beneficiary Name”).ToString,r(“PV Number”).ToString,r(“Payment Amount”).ToString)).Where(Function(g) g.Count>1).SelectMany(Function(g) g).ToList.ForEach(
Sub(r)
If (String.IsNullOrEmpty(r(“Remarks”).ToString),r(“Remarks”)=“Skipped”,r(“Remarks”)).ToString
End Sub
)

[Yoichi]

HI,

I mean as the following.

Can you try this?

Main.xaml (11.5 KB)

Regards,

[tokio_marine]

Hi Yoichi,

I got the result like this