Extracting data from separate worksheets into new worksheet

[Espilano]

I need a vba code to ease my work.

Background: I have expense report in Sheet 1 with duplicate names and raw data in Sheet 2. What I want the data to be populated in Sheet 3 are:

Employee name - to be extracted from Sheet 2
Total amount - from Sheet 1
The rest of columns in Sheet 3 to be extracted from Sheet 2

I attached the workbook for easy reference.

Thank you in advance!

[PaulM100]

You can do this with formulas as well:
Sheet 3 - A2 and down: =IFERROR(INDEX(Sheet2!$C$2:$C$17,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet2!$C$2:$C$17),0,0),0)),"")
B2 and down: =IF(A2<>"",SUMPRODUCT((ISNUMBER(SEARCH(Sheet3!$A2,Sheet1!$E$6:$E$18)))*Sheet1!$G$6:$G$18),"")
C2 and down: =IFERROR(INDEX(Sheet2!$B$2:$B$17,MATCH(Sheet3!$A2,Sheet2!$C$2:$C$17,0)),"")
D2,E2,F2 and down: VLOOKUPS wrapped in IFERRORs

[Espilano]

Hello PaulM100,

How can I change the formula if I need the name in Sheet 1 to be populated in Sheet 3?

Thank you.

[Espilano]

To be specific, the name to be populated in Sheet 3 are extracted from Sheet 2 based on the ID number. For example, the name "Janice" in Sheet 1 does not match the name in Sheet 2. Therefore, would like to use the ID number to identify the name.

[PaulM100]

I am not sure if this is what you want, but modify the formula from above for B2 into: =IF(A2<>"",SUMPRODUCT((ISNUMBER(SEARCH(Sheet3!$C2,Sheet1!$E$6:$E$18)))*Sheet1!$G$6:$G$18),"")
Ad you can add a new column to return the name found in sheet1(it is a bit long, and I am sure that there may be a easier way, but this will do the job as well:

Please Login or Register  to view this content.

[Espilano]

Thank you again PaulM100.

The formula is a bit too long It will create problem if someone in the office uses this file and did not copy the formula correctly. I would prefer a macro to do this job. The extract result that I want as below:

Employee Name Total Amount Employee ID Bank A/c No. File No.
Loh Ming Yee 236.00 8192 PBB 490855 2335477
Ung Ngee Huat 65.30 7309 PBB 496714 523612

Sorry, I do not know how to reattach the file.