Remove Duplicates in Sheets
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Remove Duplicates in Sheets
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
TIA
Sherry
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 602
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Remove Duplicates in 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. ( 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
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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.
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.
Sherry
-
- 5StarLounger
- Posts: 602
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Remove Duplicates in Sheets
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
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!
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")
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!
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Duplicates in Sheets
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).
- 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).
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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.
You do not have the required permissions to view the files attached to this post.
Sherry
-
- 5StarLounger
- Posts: 602
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Remove Duplicates in Sheets
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
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
Last edited by DocAElstein on 28 Jan 2023, 13:13, edited 10 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Duplicates in Sheets
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?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 584
- Joined: 14 Nov 2012, 16:06
Re: Remove Duplicates in Sheets
In A1657:
Autofil to A1657:G2100
Code: Select all
=IF(COUNTIF($G2:$G1656;Sheet1!$G2)=0;Sheet1!A2;"")
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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.
Sherry
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Duplicates in Sheets
Here is a macro.
Remark: this will leave the existing duplicates in the original data on FULLSHEET alone.
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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?
Sherry
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Duplicates in Sheets
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).
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).
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
Hans, that is exactly what we need. Going to try it now. Stay tuned.
Sherry
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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!
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!
Sherry
-
- 4StarLounger
- Posts: 562
- Joined: 27 Jun 2021, 10:46
Re: Remove Duplicates in Sheets
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.
>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.
-
- 2StarLounger
- Posts: 184
- Joined: 08 Feb 2010, 23:37
- Location: Howell, Michigan USA
Re: Remove Duplicates in Sheets
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?
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?
Sherry
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands