separating non duplicates

Your "test" data sheet is corrupted.  Starting at row 211, there is a phase shift in columns C:I that and the data in those sections is shifted upward.  This is overwriting/removing data.  It gets progressively worse as you move through the dataset.

That said, if you only care to copy over the unique product codes from Column A of the "test" sheet - that don't already exist in column G of the "Main" sheet, you could manually do it like so:

1) On the "test" sheet, delete columns B:I
2) Use the "Remove Duplicates" function on column A (turn off "My data has headers")
3) Delete the top three rows (DIRECT, ~~~~~~~~~, Stock Code)
4) Insert a new row on top (row 1).  Type in temporary headers in A1 and B1 ("Prod Code", "Lookup")
5) In Cell B2 insert the following formula, then copy down to the last data row:
=VLOOKUP(A2,Main!G:G,1,FALSE)
6) Turn on Auto Filtering and filter on all #N/A values in column B (click the filter drop arrow, turn off "(Select All)" then scroll to the bottom and turn on the check box for #N/A


This will show a list of all product codes that are NOT in the Main sheet in column  G.  Copy the values from column A and paste them at the bottom of column G.

If you need more data from your "test" sheet moved to the "Main" sheet, you'll need to fix your output first.

Regards,
-Glenn

FWIW, here is a modified version of your stock codes sheet.  I used data filtering to elimate the extra header rows and blank rows.  I also added a text-version of your stock code column since many codes were numeric and you might need them as text for lookups.

Regards,
Glenn
EE-stock-codes.xlsx