We help IT Professionals succeed at work.

separating non duplicates

robmarr700 asked
Last Modified: 2014-05-16
Sheet Main collumn G displays a list of product codes with several duplicates which need to remain in place.

Sheet test collumn A also displays a list of product codes many of which are duplicates of those in collumn G on the main sheet.

However their are several additional product codes in this list which do not appear in colum G (mainsheet).


I would like to separate these codes from collumn A & add them to the end of the list in collumn G (main sheet)?

Before hand I also want to remove all
Stock Code

from collumn A sheet test
Watch Question

Glenn RayExcel VBA Developer
Top Expert 2014

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:
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.



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?
Excel VBA Developer
Top Expert 2014
This one is on us!
(Get your first solution completely free - no credit card required)
Glenn RayExcel VBA Developer
Top Expert 2014

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.


Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.