PDA

View Full Version : Duplicate value removal across sheets and rows and columns IN A FILE



jiijjuu
10-07-2016, 01:09 AM
Since I could find much of a straight answer in my googling, I am turning in here for an expert help.


I have an excel file with almost 15 sheets in it, and each sheet has more than 17 columns filled with different number combinations which goes down in various row length. I am in need of help to retain only the values in cells which are unique. I need all repetitions to be deleted across columns, rows and sheets. So in effect a number like 509 should be there only one time across the whole file


Can you pls help me with your expertise.
Thanks in advance.

mana
10-07-2016, 02:51 AM
Option Explicit

Sub test()
Dim dic1 As Object
Dim dic2 As Object
Dim ws As Worksheet
Dim c As Range
Dim s, it

Set dic1 = CreateObject("scripting.dictionary")
Set dic2 = CreateObject("scripting.dictionary")

For Each ws In Worksheets
For Each c In ws.Cells(1).CurrentRegion
s = c.Value
If dic1.exists(s) Then
c.ClearContents
Set dic2(s) = dic1(s)
Else
Set dic1(s) = c
End If
Next
Next

For Each it In dic2.items
it.ClearContents
Next

End Sub

1819
10-07-2016, 03:02 PM
Cross posted here:

http://www.excelguru.ca/forums/showthread.php?6820-Duplicate-value-removal-across-sheets-and-rows-and-columns&p=27902#post27902
(http://www.excelguru.ca/forums/showthread.php?6820-Duplicate-value-removal-across-sheets-and-rows-and-columns&p=27902#post27902) http://www.mrexcel.com/forum/excel-questions/969103-duplicate-value-removal-across-sheets-rows-columns-file.html


http://www.excelforum.com/showthread.php?t=1158795&p=4497560&posted=1#post4497560

(http://www.excelguru.ca/forums/showthread.php?6820-Duplicate-value-removal-across-sheets-and-rows-and-columns&p=27902#post27902)