How to fetch specific data from other sheet using macro?

Hi all,

Hope all are well.

1 help please


In Serialisation PR Records file, in sheet DM Records, Column C will contain same number as in Column B in Serialisation - DM Report 2023 file. Based on this common data in the columns, column O, U and V in Serialisation PR Records file shall be fetched from Serialisation - DM Report 2023 file.


Note 1:

Both the files will have same file names and will be open while running macro.

Macro is required in Serialisation PR Records file


Where in Serialisation PR Records file,

Number under Column C is available under Column B in Serialisation - DM Report 2023 file with below conditions:

Column G = TSCO REQ

Column I = Complete Notification & Inform Requestor

Then,

1. Column U in Serialisation PR Records file shall show as "Completed", else "Pending"


2. Column O in Serialisation PR Records file shall be fetched from Column K in Serialisation - DM Report 2023 file


3. Column V in Serialisation PR Records file shall be fetched combined from Column N and O in Serialisation - DM Report 2023 file and populated in the below format (FYI: columns N and O in actual will be hidden)

23-01-2023 10:38:07 AM


Note2:

Column V in Serialisation PR Records file shall might give a problem since it automatically captures timestamp based on below macro. This auto timestamp is required only when data is selected in Column N thus capturing timestamp its in column Q while all other columns are to be removed from below macro.

Please Login or Register  to view this content.

Note3:

Sheet PR Records contains CF and sheet DM PR Availability contains formulae with respect to sheet DM Records. Hope they run perfectly, else i will be doomed


Pass: Abc@123


Can someone please help

Previous files to be ignored.

Sorry i guess the task is bit challenging but can someone respond

I was wondering if this can be done using XLOOKUP, but it didnt workout



To simplify my requirement, let me reiterate:

Note:

Both the files will have same file names and will be open while running macro.

Macro is required in Serialisation PR Records file.



In Serialisation PR Records file, 3 columns are to be populated from Serialisation - DM Report 2023 file i.e. Columns I, N and O.


1st check:


If value under Column B (DM No.) in Serialisation PR Records file is found in Serialisation - DM Report 2023 file.


If yes, then this value (DM No.) will appear altleast 5 times in Serialisation - DM Report 2023 file.


1. Column I in Serialisation PR Records file shall be fetched from Column K in Serialisation - DM Report 2023 file where the value against the respective DM No. in Column I is "Perform Independent Check".

Row highlighted in yellow.


2. Column N in Serialisation PR Records file shall show as "Completed", if the value against respective DM No. in Column G is "TSCO REQ", else "Pending".


Row highlighted in yellow shows as TSOS REQ, hence value in Column U in Serialisation PR Records file is PENDING. Also, since its pending, Column O should be blank.


3. Column O in Serialisation PR Records file shall be fetched combined from Column N and O in Serialisation - DM Report 2023 file if the value against DM No. in Column G is "TSCO REQ".

Date to be populated in the below format (FYI: columns N and O in Serialisation - DM Report 2023 file will be hidden)

23-01-2023 10:38:07 AM


Row 154 in Serialisation PR Records file highlighted in green matches all conditions with row 20 in Serialisation - DM Report 2023 file, hence it shows as Completed and shows the date and time.

Pass: Abc@123

Can someone please help