Link to home
Start Free TrialLog in
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
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

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
Avatar of robmarr700
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
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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