I want to concatnate three rows in excel and want to write in another coloumn,suppose i have three coloumns NAME,ROLE,COMPANY.in 4th coloumn i need all three rows to be concatnate

excel
activities

#1

i want to concatnate three rows in excel and want to write in another coloumn,suppose i have three coloumns NAME,ROLE,COMPANY.in 4th coloumn i need all three rows to be concatnate


#2

Typically you wouldn’t concatenate the rows because this would be combining different sets of data.

The excel formula for concatenate is just [cell]&[cell]. You could write this formula into column 4 and read the value back into the workflow.


#3

Actually my problem is to get Duplicates from Multiple coloumns ,i Have expression to get duplicate for single coloumn ie (From p in dt.Select() where( From q in dt.Select() where q(“Coloumn Name”).Equals(p(“Coloumn Name”)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable() but i want for multiple coloumns,i tried many ways i didnt get the output so i am concatinating that multiple coloumns and iam getting duplicates from concatinated coloumn


#4

Hi, @charan.nvd

I am checking the duplicate for multiple columns using this code:

dtMasterFile.Select(“Account_Number=’” + strAccNumber + “’ AND Close_Date=’” + strCloseDate + “’ AND Dummy_Name=’” + strDummy+ “’” )

Variables: strAccNumber, strCloseDate, strDummy

So basically, this is the step I used:

  1. Use Excel Application Scope
  2. Read Range and put value in runtime datatable
  3. Extract all 3 columns separately - using ‘For Each Row’ and ‘Get Row Item’ activities.
    strAccNumber = dtExcelFile.Rows(intRow).item(0)
    strCloseDate = dtExcelFile.Rows(intRow).item(1)
    strDummy = dtExcelFile.Rows(intRow).item(2)
  4. I used ‘Assign’ for the code above - create a variable with ‘DataRow[ ]’ datatype
    drFilteredFile = dtMasterFile.Select(“Account_Number=’” + strAccNumber + “’ AND Close_Date=’” + strCloseDate + “’ AND Dummy_Name=’” + strDummy+ “’” )
  5. Then use ‘IF’ to validate if it returns value
    [a] Since I am using a different datatable to compare my excel with, my validation is
    If drFilteredFile.Count = 0 Then UNIQUE
    [b] If you are using the same excel to filter, have to adjust count to 1
    If drFilteredFile.Count = 1 Then UNIQUE
    For [b], you also don’t need to use variables. You can replace the variables in Step 4 with the assigned value directly - dtExcelFile.Rows(intRow).item(1)

Hope this is the one you are looking for.

Regards,
Mharlieee


#5

thank u so much for responding @Mharlieee,if u dnt mind can u plz share the xml file,where u used


#6

Hi, @charan.nvd

Check this one.Main.xaml (15.3 KB)

This is the sample excel file.
Audit.xlsx (9.2 KB)

Regards,
Mharlieee


#7

thank u @Mharlieee