Merging duplicate coloumns in excel

Help Studio
, ,
1

Hi UiPath team,

i have an excel sheet like this

A spreadsheet in a program containing passport and visa details along with phone numbers and nationality under Sheet2 tab. (Captioned by AI)
A spreadsheet in a program containing passport and visa details along with phone numbers and nationality under Sheet2 tab. (Captioned by AI)660×719 6.72 KB

as you can see in the screenshot the coloumn expiry date comes two times the expiry dates are based on the passport and visa i need to rename the coloumn as Passport expiry date and Visa Expiry date i need final coloumn like this
An Excel spreadsheet lists passport expiry dates, passport numbers, visa expiry dates, and visa numbers, with a warning sign indicating a potential issue in one of the cells. (Captioned by AI)
An Excel spreadsheet lists passport expiry dates, passport numbers, visa expiry dates, and visa numbers, with a warning sign indicating a potential issue in one of the cells. (Captioned by AI)679×248 3.55 KB

please help me to find the solution

2

@Alan_Prakash1,

If your column sequence is fixed follow this approach

  1. Use Excel Process Scope activity, inside it use Use Excel file activity - give the excel file path
  2. Use Read cell activity to read existing value of the cell A2 into a variable for example strValue
  3. Use write cell activity to write "Passport "+ strValue at cell A2

Do same for visa as well. Just cell address will change

3

we cant assume the passport and visa present in fixed coloumn it may change i need a dynamic solution

4

@Alan_Prakash1,

Is the sequence of column going to be fixed? There should be something fixed which will identify which expiry date is of which.

5

i mean that in this sample excel i have given expiry date in A2 but it is not always like it may be in b2 ,c2 like that so is any way we can make it as dynamic

6

@Alan_Prakash1,

We can but require some reliable logic to identify it otherwise bot could be unreliable.

7

do u have any idea how to do this i am trying with invoke code and linq but not able to find the solution

8

how about using the activity “Find/Replace Value” ?
I tested your case, see the sequence in picture:

This image shows a workflow configuration for automating the process of finding and replacing values in an Excel sheet. (Captioned by AI)
This image shows a workflow configuration for automating the process of finding and replacing values in an Excel sheet. (Captioned by AI)839×788 39.6 KB

9

@Alan_Prakash1
by using below linq code you can serach dynamic column you will get the column index as output

(From row In dt_bankstatement.AsEnumerable(), col In dt_bankstatement.Columns.Cast(Of DataColumn)().Select(Function(c, index) New With {Key .Column = c, Key .Index = index}) Where row(col.Column).ToString().Contains(element_tosearch.Trim) Select col.Index).FirstOrDefault()

element_tosearch will be either visa or passport

cheers!!