Remove Duplicates in Sheets

[sobershea]

I have 2 spreadsheets with one column in common. I want to do a formula that will compare that common column between the two sheets and identify the duplicates in sheet 2. The purpose is to append to the end of sheet 1, the unique items from sheet 2. Is it possible to compare with a formula between 2 sheets?

TIA

[HansV]

Which version of Excel do you have?

[DocAElstein]

Is it possible to compare with a formula between 2 sheets

Hello
I am not so hot on Excel formulas, but as far as formulas go, working between worksheets, it is not usually a problem converting a formula that works for a couple of columns in a worksheet to make it work for column(s) in other worksheets, if that is what you are asking?
You just change the range reference to include the worksheet.

Like If I had in column A in worksheet Sheet1 this
F-Tang
P-Tang
OLE
Biscuit-Barrel
Pooh
, and then in worksheet Sheet 2 this in column A
OLE
Wolly
Woggy
Pooh
, then I can put this formula in cell B1 of worksheet , sheet2 , and drag it down.
=IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$5,0)),"","Dupy")
It will tell me if any value in the second sheet is already in the first worksheet.

DupyPooh.JPG

( That formula will work in all versions I think, including all older ones. I expect there is a better way to do the thing in newer versions , probably why Hans is asking )

Alan

[sobershea]

2010 Excel.

And I meant to say the info is in 2 different workbooks. So the Formula from Alan would work if I copied the sheet from workbook 2 to workbook 1 or vice versa. I think.

[DocAElstein]

Same sort of thing again, just a bit of a funny syntax.
Say DupyPooh.xls has just the original worksheet , Sheet1

Instead of Worksheet Sheet2, you have second workbook Book2.xls

If the two workbooks are open the formula cell B1 of Book2.xls is
=IF(ISERROR(MATCH(A1,[DupyPooh.xls]Sheet1!$A$1:$A$5,0)),"","Dupy")

If DupyPooh.xls is closed you need something like

Code: Select all

 =IF(ISERROR(MATCH(A1,'F:\Excel0202015Jan2016\OffenFragensForums\eileenslounge\Formulas\[DupyPooh.xls]Sheet1'!$A$1:$A$5,0)),"","Dupy")

You need to change that path to the one to go where you have the closed workbook.

By the way there is a simple trick to get that awkward long formula for the closed workbook: Have both workbooks open, use the formula for when both workbooks are open, then close DupyPooh.xls, and Excel conveniently shows the closed workbook formula where the open workbook formula is, so you can then copy that, delete that formula, and paste it back in!

[HansV]

Let's say that
- The common column is A.
- The data on sheet 1 are in A2:A50.
- The data on sheet 2 are in A2:A100.

Open both workbooks.
Enter the following formula in A51 on sheet 1 and confirm it by pressing Ctrl+Shift+Enter to turn it into an array formula.

=IFERROR(INDEX('[OtherBook.xlsx]Sheet2'!$A$2:$A$100, MATCH(0,COUNTIF(A$50:A50, '[OtherBook.xlsx]Sheet2'!$A$2:$A$100), 0)),"")

Replace OtherBook.xlsx with the name of the other workbook and Sheet2 with the real name of that sheet.
Fill down at least 100 rows.

Then first close the other workbook (saving it if necessary).
Excel will add the path of that workbook to the formulas in the first workbook.
Save the first workbook (the one with the formulas).

[sobershea]

In the attached file, FULLSHEET is the master file, and Sheet1 is from another workbook. I want to identify all items in Sheet1 which have a unique # in column G compared to all the numbers in Column G in FULLSHEET. The unique items will then be appended to FULLSHEET.

[DocAElstein]

I am afraid that with my low capacity brain it would take me a week to play with a big file like that. If you give a much smaller example and explain a bit clearer exactly what you want, I could take a look.

By the way, did you look at or try anything we suggested so far?

Alan


Edit, just one short tip I remembered. Its actually very easy the get the correct reference you new to a different worksheet or workbook, open or closed.:-

_ Open any two books
_ Go to any cell in either workbook and type a
=
, in any cell

_ now got to the other workbook and click on any cell, ( or select a range of cells )
_ Hit Enter

Bingo! That’s it. Click back on the original cell you put the = into. Look in the formula bar – Excel has put the full reference in for you. It should look something like
=[BookName.xls]Sheet7!$C$4
Close that other workbook and the formula changes to look like it should to access a closed workbook cell. Something like
='C:\FolderName\[BookName.xls]Sheet7'!$C$4
edit... strangely something important is not showing as it should there should be two ' bits in that formula ... try it again a bit differently
='C:\FolderName\[BookName.xls]Sheet7'!$C$4
OK I think that last one is better, you can see clearly the two ' bits

[HansV]

Would a macro be OK?

[HansV]

There are some duplicate rows on Sheet1 with the same VIN but different colors (silver vs black). Does it matter which one would be copied to FULLSHEET?

[snb]

In A1657:

Code: Select all

=IF(COUNTIF($G2:$G1656;Sheet1!$G2)=0;Sheet1!A2;"")

Autofil to A1657:G2100

[sobershea]

Hans, a macro would be fine. And the color on sheet 1 has no bearing. I just want sheet1 to somehow tell me which ones are not on the FullSheet so I can append the unique ones to the FullSheet. A formula would work as well.

[HansV]

Here is a macro.

Code: Select all

Sub CopyUnique()
    Dim wS As Worksheet
    Dim wT As Worksheet
    Dim s As Long
    Dim m As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set wS = Worksheets("Sheet1") ' or Workbooks("OtherWorkbook.xlsx").Worksheets("Sheet1")
    m = wS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set wT = Worksheets("FULLSHEET")
    t = wT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For s = 2 To m
        If wT.Range("G:G").Find(What:=wS.Range("G" & s).Value, LookAt:=xlWhole) Is Nothing Then
            t = t + 1
            wS.Range("G" & s).EntireRow.Copy Destination:=wT.Range("A" & t)
        End If
    Next s
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Remark: this will leave the existing duplicates in the original data on FULLSHEET alone.

[sobershea]

Hans, I'm trying to follow along but am not getting it entirely. To clarify: FULLSHEET is the master; Sheet1 in the SAME workbook is the new data of which some of it is duplicates of FULLSHEET. It looks like your macro is identifying the dups on Sheet 1 and appending them to the next row in FULLSHEET?

[HansV]

No, it takes the (unique) VINs in column G of Sheet1 that do NOT occur in column G of FULLSHEET and adds their rows below the existing rows of FULLSHEET. Is that what you wanted?
The code as written assumes that Sheet1 is in the same workbook, but I indicated how you can modify it if Sheet1 is in another workbook (that should be open in Excel when you run the macro).

[sobershea]

Hans, that is exactly what we need. Going to try it now. Stay tuned.

[sobershea]

Three Cheers to everyone who helped!!! I ended up using the Macro from Hans. The Macro works perfect! I actually did it manually just to proof the results and my laborious manual operation matched the macro exactly.


This spreadsheet is used by my sister for a small business and the way the data is handled is a bit awkward and they are somewhat newbies when it comes to what Excel can do. I wish you could have seen her face when she ran the macro for the first time!


So, thank you all from my sister and me!

[SpeakEasy]

Late to the party I know, but quick question, and it is something that Hans picked up on earlier:

>existing duplicates in the original data on FULLSHEET alone

Is it correct that FULLSHEET should have duplicate VINs? Or is that the next data cleaning exercise? I ask because there is a two or three line non-looping solution to your original requirement if FULLSHEET is NOT supposed to have any duplicates.

[sobershea]

Yes, we addressed the issue of duplicates on the FULLSHEET. However, there are 2 kinds of duplicates; the first, human error; the second, true duplicates because the car has been purchased (one line), returned (a second line/instance), and purchased again (third instance).

As we were resolving the duplicates we realized that these were OK for the purposes of this sheet. What it means is that this is a situation that must be viewed on a case by case basis.

Thank you for noticing and commenting. Hans, I don't think this affects the macro, right?

[HansV]

No, that doesn't affect the macro at all.