Compare two columns on separate sheet, if missing, insert missing data

I've been searching for help for so long, and I thought I finally came across some code to do the job. The problem is that I'm not very experienced in VBA, so I'm still not able to get it to work.

My process

• Copy data range from Sample Data tab
• Paste into Data tab
• I'd like code which will compare the data in Column A of Data tab to Column A of Tracker tab, and then insert any missing data to the next available blank cell in Column A of Tracker tab.
• The end result would be the same list of data in Columns A on both the Data and the Tracker tabs

Here is the code I'm using (currently on the Data tab)

HTML Code: 

Any help would be greatly appreciated!!

OllieB - Thank you for your quick response! Two questions:

1. Where should the code be stored?
2. What do you consider a large number of values? I will have no more than 5,000 records in column A

OllieB - I can't thank you enough. Your solution works flawlessly!

OllieB - Can you let me know what changes I'd need to make if I wanted everything to remain the same, EXCEPT to insert any missing data to the next available blank cells in Columns A AND B of Tracker tab? In other words, the validation to compare the lists would still be on Column A, but if missing from Tracker tab, it would insert both the Item number (Column A) and the Item Name (Column B).

Column A is an item number
Columns B is a corresponding item name

Thank you again.

OllieB - That is brilliant, once again. Thank you!!

OllieB - I've been using your code and it's been working great. There is one small problem that I'm hoping you might be able to help with. The data in Column A are 6-digit ID numbers, many of which have leading zeroes. When the data from Column A of the Data tab are pasted in to Column A of the Tracker tab, the leading zeroes disappear. Column A in both tabs is formatted as text, which is what I want, in order for lookup formulas in other columns to work properly.

Any suggestions?

OllieB - That is PERFECT! I can't thank you enough. Appreciate you help and lightning fast responses.

Hello All!

I was hoping someone could help me find out where I am going wrong with this. I am trying to compare two lists doing exactly as the OP stated above, however I have 4 columns of data and each row is linked to each other. For example, I have data is A1:B1:C1:D1 and I need to compare this group with each line of data on the Tracker tab. I ran this code and ran into several problems:

- If there is multiples of the same data in the DATA tab and only 1 set on the Tracker tab, it will insert another set of the data in the Tracker tab.
- The code sometimes will not work at all.

Basically what I am trying to do is gather 7 days of data (that will have the same values on certain days) and compile it into one master list to use. Anyone have any ideas?

Please Login or Register  to view this content.