Excel Automation _ Join rows _ Merge Rows _ compare rows

Help Studio
, , , ,
1

Hi Team,

I need your help to fix issue on my project.

I have an excel sheet and it have some values.

Considering the sheet 1 - I have data on row number 2 and 3 will have same HPID and DOS, with the unique value I need to merge both the 2 and 3 row into a single row mentioned in the output sheet.

I have attached the sample excel.

Thank you,
Mani
Sample.xlsx (10.2 KB)

2

Hi @manikandan.murugan ,

  1. Read the Excel Data:
Excel Application Scope - ExcelFilePath
    Read Range - "Sheet1" - Output: dt
  1. Process the Data:
Assign - mergedDataTable = dt.Clone()

For Each rowGroup In dt.AsEnumerable().GroupBy(Function(r) Tuple.Create(r("HPID"), r("DOS")))

    Assign - newRow = mergedDataTable.NewRow()
    Assign - newRow("HPID") = rowGroup.Key.Item1
    Assign - newRow("DOS") = rowGroup.Key.Item2
    Assign - newRow("UniqueColumn") = String.Join(",", rowGroup.Select(Function(r) r("UniqueColumn").ToString()))
    
    Add Data Row - DataTable: mergedDataTable - DataRow: newRow

Next
  1. Write the Result to the “Output” Sheet:
Excel Application Scope - ExcelFilePath
    Write Range - "Output" - mergedDataTable
3

Hi @sandyarpa767 ,

Thank you so much for your help. I will be trying the same.

Regards,
Mani

4

I could not understand this

5

use this in for each activity
rowGroup In
dt.AsEnumerable().GroupBy(Function(r) Tuple.Create(r(“HPID”), r(“DOS”)))

6

RowGroup is List of items I am I right

7

Yes, you’re correct! In the context of the For Each activity iterating over the grouped rows, rowGroup represents a collection of rows (essentially a List of DataRow objects) for each unique combination of “HPID” and "DOS

8

While I am running I am getting this error

For Each: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

9

Hi @manikandan.murugan

Retype the double quotes in that expression

Regards,

10

Thanks for your help @lrtetala

11

I have an error as
Assign: Column ‘UniqueColumn’ does not belong to table DataTable.

12

The error “Column ‘UniqueColumn’ does not belong to table DataTable” typically occurs when you try to access or assign a value to a column that doesn’t exist in the DataTable. To resolve this issue, you need to ensure that the UniqueColumn exists in both the original DataTable (dt) and the cloned DataTable (mergedDataTable)

13

Hi @sandyarpa767 ,

To help you out on this problem I need a few inputs from your end.

  1. while going through your given input file there are a few columns which getting accumulated(like “AcceptableDOSReviewed”, “NoOfVisits”), as other column values are empty so I need one clear instruction.
  2. The columns (like “ClaimsLinked” etc) where date is appearing how we need to pick the result date in the output unique row (example which one will be the recent date of from the row sequence in input file the last one row date).

image
image1507×242 10.6 KB

Note: please let me know the points if I missed something which I have to take care of it.

14

Hi @Sagar1

Thanks for your initiative on my issue.

I have completed the same and it’s working fine now.

Happy Automation