[Solved] Remove ALL duplicate Entries

Discuss the spreadsheet application
Post Reply
Dragoon09
Posts: 5
Joined: Tue Feb 01, 2011 2:02 am

[Solved] Remove ALL duplicate Entries

Post by Dragoon09 »

Hello, Let me start by saying I have been searching for an answer on and off for months trying to find an answer that works - many formulas I have found don't seem to work or do what I need them to do. with that said, here is my query:

I have two spreadsheets: 1 is my inventory, the other is my suppliers inventory. Every so often, I need to compare the two.

I need to find a way to compare them and remove ALL duplicate entries so that what is left is new products that I haven't yet put in my inventory.

The other formulas or filters or I have tried only seem to remove the second duplicate, leaving the original value. Or returning a result of 1 or 0 to indicate duplicate.
This is not what I am looking for.
If the model number exists in BOTH files/sheets, then it needs to be removed/deleted from BOTH.
If the model number exists in 1 file, then it should remain.

Any help is greatly appreciated
Matt
p.s. For an analogy of what I need think of this: When you match your socks, you always have at least 1 without a match, right? Well, I need the ones without a match.
Last edited by Dragoon09 on Mon Nov 21, 2011 9:06 am, edited 1 time in total.
Open Office 3.2
Windows Vista
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Remove ALL duplicate Entries

Post by acknak »

Dragoon09 wrote:I have two spreadsheets: 1 is my inventory, the other is my suppliers inventory. ...
I need to find a way to compare them and remove ALL duplicate entries so that what is left is new products that I haven't yet put in my inventory.
Ok, so all inventory items (I) should be present in the supplier's list (S), no?

If so, you can ignore I, and the problem boils down to finding the items in S that are also in I. If you have a formula that tells you that, that a specific item in S is also in I, then what more do you need? There's no formula for deleting cells. You can use the result of the formula to sort, or filter, or otherwise separate out those items in S, then copy/paste them to make a list of new items.

Maybe I've misunderstood what you want.
AOO4/LO5 • Linux • Fedora 23
Dragoon09
Posts: 5
Joined: Tue Feb 01, 2011 2:02 am

Re: Remove ALL duplicate Entries

Post by Dragoon09 »

Thank you for your swift reply ACKNAK.
If I understand you correctly, your close to what I need. Perhaps removed/deleted is a bit strict. Basically I need to know what items are in the suppliers file that aren't in mine.

From what I've gathered over time using Standard Filter to highlight or hide duplicates, this solution only hides/removes the 2nd identical value
If I use an =if(a1=b1;"1;0){or similar} then sort, I still get a similar result to the option above.
If there was a way to use the IF statement and have it say, maybe, 1 for both matching numbers, and 2 for unique numbers, then I could sort. That would work, but don't know how to code something like that.
Open Office 3.2
Windows Vista
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Remove ALL duplicate Entries

Post by kingfisher »

You should be able to adapt the formula in this topic.
Apache OpenOffice 4.1.9 on Linux
Dragoon09
Posts: 5
Joined: Tue Feb 01, 2011 2:02 am

Re: Remove ALL duplicate Entries

Post by Dragoon09 »

I had actually come across that page before, and for some reason those formulas weren't working for me.
This time, I did some trial and error a little difftently and finally figured it out.

This will help alot and save me lots of time and energy.

Thanks to All who helped out - inlcuding the guys in the other post!
Matt

ps - the code that worked for me was:
=VLOOKUP(A1;Sheet1.$A$1:$A$6000;1;0)
making sure that my inventory list was in sheet 1 and supplier's master list in sheet 2
Open Office 3.2
Windows Vista
Post Reply