VBA to lookup,Match and update the values from another sheet

[aaleem]

Hi,
I have a file that contains 100K+ records for various logs. Currently, it is taking a long time to validate each user and update the Status, Job Description, and responsibility.
I have given the below sample of two sheets along with the expected results.

is there a possibility to automate this thru VBA?

Any help will be highly appreciated.

Sheet 1 (Result Sheet) where the update is required.

Index Match VBA based on condition.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Supplier Name	Quantity	Total Unit Cost	Comb.Status	Username	User job desc	Dept	Responsible
2	SAJU Trading LLC	42	8382.042	Pending QA	Tom
3	SAJU Trading LLC	62	28462.473	DSD Log	Tom
4	Allex Enterprises LLC	1	236.061	Pending match	Fred
5	SAJU Trading LLC	13	2225.781	Pending QA-DSD	Glasgo
6	SAJU Trading LLC	38	9791.811	Pending QA-DSD	Glasgo
7	SAJU Trading LLC	43	7869.609	Pending QA	Fred
8	SAJU Trading LLC	42	8382.042	DSD Log	Mike
9	Trading LLC	42	8382.042	Pending match	Mike
10	Trading LLC	42	8382.042	NO GRN	Tom
11	Trading LLC	42	8382.042	GRN Approved	Tom
12
13
14		VBA Conditions
15			For each user in Col. F (Username) If User name match from Status sheet, If not match ignore the row
16			Then Compare the Comb. Status with Status Sheet Col E (Status to be)
17			Update the Comb. Status (Col D in Result sheet) by copying the (Status to be) from Status Sheet
18			Also to update the User Job desc, Dept and Responsible column by copying from the Status sheet for respetive users.
19			(Excel Version 2016)
20	Below is the expected result
21
22	Supplier Name	Quantity	Total Unit Cost	Comb.Status	Username	User job desc	Dept	Responsible
23	SAJU Trading LLC	42	8382.042	Pending with Logistics	Tom	Logistics Manager	Logistics	Harry
24	SAJU Trading LLC	62	28462.473	Pending with Logistics	Tom	Logistics Manager	Logistics	Harry
25	Allex Enterprises LLC	1	236.061	Pending with Logistics- No GRN	Fred	Logistic Executive	Logistics	Harry
26	SAJU Trading LLC	13	2225.781	Pending with Buying	Glasgo	Planner	Buying	Tim
27	SAJU Trading LLC	38	9791.811	Pending with Buying	Glasgo	Planner	Buying	Tim
28	SAJU Trading LLC	43	7869.609	Pending with Logistics- No GRN	Fred	Logistic Executive	Logistics	Harry
29	SAJU Trading LLC	42	8382.042	DSD Log	Mike
30	Trading LLC	42	8382.042	Pending match	Mike
31	Trading LLC	42	8382.042	Pending with Logistics	Tom	Logistics Manager	Logistics	Harry
32	Trading LLC	42	8382.042	Pending with Logistics	Tom	Logistics Manager	Logistics	Harry
Result

Status sheet (to lookup the values)

Index Match VBA based on condition.xlsx
	A	B	C	D	E
1	Username	User job desc	Dept	Responsible	Status to be
2	Tom	Logistics Manager	Logistics	Harry	Pending with Logistics
3	Fred	Logistic Executive	Logistics	Harry	Pending with Logistics- No GRN
4	Glasgo	Planner	Buying	Tim	Pending with Buying
5
6
7
8
9
10
11
Status

Thanks
aleem