Transferring of multiple column values from one excel sheet to another excel sheet

Help Activities
, ,
1

Good Day.

image
image801×654 40.1 KB

I have this datatable (SourceSheet) where it consists a list of employees together with Employee ID, Employee Name, Position, and Department. I want to copy the “Employee Name”, “Position”, and “Department” from SourceSheet to WorkingSheet through Employee ID. Can I use LINQ? Or are there any other activities that can be used such as Filter DataTable?

Kindly see attached picture below for the desired output and attached file for the excel file. Thank you.

Note: The highlighted part is the desired output.

image
image1920×1005 104 KB

SampleExcelFile.xlsx (180.8 KB)

Best regards,
Robert Monsalud

2

in filter datatable there option is called keep or remove column please kindly check that

1 Like
3

Can you elaborate it? Thank you.

Best regards,
Robert Monsalud

4

filtertable
filtertable1271×747 24.5 KB

in that picture you can see just click on output columns and give column names one by one which you want to keep

1 Like
5

Is Filter DataTable activity the only activity that I can use?

6

you can use read column but this is better and efficient way

1 Like
7

Hey,
Please use this code
SampleExcelFile.xlsx (180.6 KB)
Main.xaml (11.3 KB)
It is working for me.

Thanks

1 Like
8

@Robert_Russell_Monsalud
If you are able to get as your Expected Output
Then please accept the solution
Thanks,
Rounak

1 Like
9

May I ask if there is any other faster or more efficient approach because it takes a lot of time to finish the process?

10

Read Range into a datatable.

Remove Data Columns (twice) to delete the Project Number and Employee ID columns from the datatable.

Write Range (or Append Range, depending on your needs) to the new sheet.

1 Like
11

Is there any other way for this scenario aside from Lookup activity?

1 Like
12

Hey
I think it is the appropriate way to get the output
Thanks

13

vba method:
the logic is simple, just fill in vlookup formula in the first row and drag it all the way down, it takes an instant to fill in 3000 rows:

image
image877×250 13.8 KB

Steps

  1. copy this code to vba.txt (same folder as your project)
Function Macro1()
    
    ActiveWorkbook.Sheets("WorkingSheet").Activate

    Range("C2").Formula = "=VLOOKUP($B2, SourceSheet!$A:$D, 2, FALSE)"
    Range("D2").Formula = "=VLOOKUP($B2, SourceSheet!$A:$D, 3, FALSE)"
    Range("E2").Formula = "=VLOOKUP($B2, SourceSheet!$A:$D, 4, FALSE)"
    Range("C2:E2").Select
    Selection.AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row) 
    ActiveWorkbook.Save
End Function
  1. use this sequence to call the vba function on your excel
    Sequence1.xaml (5.3 KB)

result

image
image777×441 23.3 KB

NOTE
if you get this error
image
you need to enable trust access (just do this once)

2 Likes
14

Good Day, Everyone.

Aside from the help and guidance of Sir @jack.chan, Sir @Rounak_Kumar1, Sir @Veera_Raj, and Sir @postwick, I also finally came up with a solution. I used the VLOOKUP Formula in Microsoft Excel.

Kindly see below attached files for the workflow, picture of the desired output, and the excel file being used. Thank you.

image
image990×646 44.9 KB

Main.xaml (13.0 KB)
SampleExcelFile.xlsx (180.6 KB)

Best regards,
Robert Monsalud

2 Likes
15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.