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

Good Day.

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.

SampleExcelFile.xlsx (180.8 KB)

Best regards,
Robert Monsalud

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

1 Like

Can you elaborate it? Thank you.

Best regards,
Robert Monsalud

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

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

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

1 Like

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

Thanks

1 Like

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

1 Like

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

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

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

1 Like

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

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:

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

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

2 Likes

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.

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

Best regards,
Robert Monsalud

2 Likes

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