VBA To Set Cell Value Based On Multiple Conditions

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.