In Excel particular serial need to search and get the relevant data

Help
, ,
1

Hi Team…
Kindly help me with the bellow scenario.
I have 130 serial numbers in the A-Excel sheet.
I want to find the serial number with respective cubical no, username and floor.
I have all data, serial number, cubical no, floor no, in another excel sheet name as B
I want to search for these 130 serial numbers and output should be indifferent excel name as C with a cubical number, floor, and serial number.

Kindly help me how do I do this

Thanks
Mohammed Ali

2

Do you want someone to do this? or you need help around understanding how to do this?

3

Fine
hope these steps would help you resolve this
–use a excel application scope and pass the file path of first excel as inputt
–inside the scope use read range activity and get the output with a variable of type datatable named dt1
–now use another excel application scope and pass the file path of second excel as input and get the output with variable of type datatable named dt2 using read range activity
–now use build datatable activity and create three columns like
cubical number, floor, and serial number. and get the datatable as finaldt
–followed by this use CLEAR DATATABLE activity and mention as finaldt
–now use a for each row loop and pass the above variable dt1 as input
–inside the loop use LOOKUP DATATABLE activity and mention in its property panel like this
input = row(“serialnumbercolumnname”).ToString
datatable = dt2
lookupcolumn name = “cubical no columnname” \mention the exact columnname
rowindex = int_rowindex \ int32 variable

–now next to this use a ADD DATAROW activity where in the arrayrow property mention as dt2.Rows(int_rowindex).ItemArray and in the datatable mention as finaldt
–nxt to this FOR EACH ROW loop use a write range activity and mention the input datatbale as finaldt and enable the add headers property in the property panel of write range

kindly try thiis and let know for any queries or clarification
Cheers @mohammed_ali1

4

Hi Kaderms,

Thanks for the replay.
Actually I have tried to do my self , I have created a Excel scope and save the data in one variable . Select the for each row activity , and take the excel which i have data using excel scope. then i struck in the middle like how do i search the particular serial number in my full data sheet , using the VB. can you please make a sample and show me how to do that.

5

Hi Palniyappan,

Thanks for the replay.
Actually I have tried to do my self , I have created a Excel scope and save the data in one variable . Select the for each row activity , and take the excel which i have data using excel scope. Till I got understand after that , i am unable to follow, please guide me with a simple example.

6

Sure @mohammed_ali1. I can help you with that.

Can you share some screenshots to show where you are stuck? I can provide your suggestions based on that.

Meanwhile, out of the rough context that I got from your reply, you might need Filter Datatable or Lookup Datatable activities. Here is a content that I explained a bit on Lookup datatable on another post, if you are trying to explore by yourself in the meantime.

7

Hi @mohammed_ali1,

You can use the find activity to search the serial.

Regards
Balamurugan.S