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
robmarr700
ASKER
Thanks for your reply Glenn,
The reason there is a phase shift is that I tried to remove all the blank rows and it didn't quite work as planned.
I have reattached the file before I removed the blank rows.
How would I remove the blank rows so the data lines up how it does originally therefore not corrupting the data? stock-codes.xlsx
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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.
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,FAL
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