Avatar of robmarr700
robmarr700
 asked on

separating non duplicates

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

Q.

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
DIRECT
~~~~~~
Stock Code

from collumn A sheet test
vlookup-back2.xlsx
Microsoft Excel

Avatar of undefined
Last Comment
Glenn Ray

8/22/2022 - Mon
Glenn Ray

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
ASKER CERTIFIED SOLUTION
Glenn Ray

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Glenn Ray

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23