Combine data on new tab if criteria is met

[Nanogirl21]

I have 3 worksheets: Old, RYG, and Differences


How would I copy and paste values from Old and RYG that meet certain criteria onto the Differences sheet and record the name of tab the data was originally from?

Formulas are easier for me to understand and my preferred method, but VBA can be used if necessary. I am using Office 365.


Lookup Set 1

Action: Clear all data (except row 1 headers) on the Differences sheet

Lookup: All values from column A on Old and RYG sheets

Paste: Column A on Differences sheet

Record: Name of the original sheet in column B of Differences sheet



Lookup Set 2

Lookup: All values from column N on Old and RYG sheets that contain the word RED

Paste: Column F on Differences sheet

Record: Name of the original sheet in column G of Differences sheet



Lookup Set 3

Lookup: All values from column N on Old and RYG sheets that contain the word YELLOW

Paste: Column L on Differences sheet

Record: Name of the original sheet in column M of Differences sheet


Thank you

 

[Radoslaw Poprawski]

minisheets or link to sample file please.