PDA

View Full Version : Need help constructing a vba to remove duplicates and its original and spare the uniq



estatefinds
12-03-2016, 01:43 PM
Hello, I have data in column C at row 20 that has data that has Duplicates and also has unique data.
So I need a VBA that removes the duplicates of data, so for example if there is a 1-1-A and it occurs twice or three times or four times etc, this 1-1-A would be removed. then once all of these are removed all the unique once occurring data would be placed in Column D at row 20.

I thank you in advance!:)

SamT
12-03-2016, 02:16 PM
Option Explicit

Sub VBAX()
Dim DataRng As Range
Set DataRng = Range(Range("C20"), Cells(Rows.Count, "C").End(xlUp))

Dim Cel As Range
Dim Found As Range

For Each Cel In DataRng
If Not Cel = "" Then
Set Found = DataRng.Find(Cel, Cel, , , , xlPrevious)
If Not Found Is Cel Then _
Found.Delete xlShiftUp
End If
Next

Set DataRng = Range(Range("C20"), Cells(Rows.Count, "C").End(xlUp))
DataRng.Offset(, 1) = DataRng

End Sub

SamT
12-03-2016, 02:18 PM
Another, arguably faster, option is to Filter the DataRng Unique to D20.

That leave the issue of removing Duplicates from Column C.

Combine the code above, (with ClearContents instead of Delete,) then Filter.

estatefinds
12-03-2016, 02:28 PM
Ok I ran it but when i did the 1-1-A for example is still in the list this one should also not be present as anything that had a duplicate gets removed. so for example if the data for example 1-1-A ever had a duplicate of 1-1-A whether it had one duplicate or occurred three times or four times or fives the duplicate would be removed as well as the the unque value 1-1-A and the only data that would remain are the ones that never had duplicates for example 3-29-G will remian cause it never had a duplicate. so anything that had a duplicate cancels out and is removed.

let me know if you have any questions thank you:)

Kenneth Hobs
12-03-2016, 04:02 PM
In a Module:

Sub AbsUnique()
Dim r As Range, uv As UniqueValues
Dim c As Range, a, i&

Set r = Range("C20", Range("C20").End(xlDown))
r.FormatConditions.Delete
Set uv = r.FormatConditions.AddUniqueValues
uv.DupeUnique = xlUnique
uv.Interior.ColorIndex = 3 'Red

i = 0
ReDim a(1 To 1)
For Each c In r
If c.DisplayFormat.Interior.ColorIndex <> 3 Then
i = i + 1
ReDim Preserve a(1 To i)
a(i) = c.Value
End If
Next c
r.FormatConditions.Delete 'Remove added format condition

On Error Resume Next
Range("D20").Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
End Sub

estatefinds
12-03-2016, 04:35 PM
how do i make sure all the ones that were ever a duplicate get removed entirely? I ask cause the 1-1-A still shows up in the list i also tried another one the 1-16-E and that one was a duplicate and shows up in the list. is there a way if it was a duplicate to remove them entirely? I had trouble with this for the longest time. i see it currenly removes duplicates but it needs to be removing if the data is a duplicate; it needs to remove it meaning it doesnt exist in the data.

so if it was ever a duplicate it would remove that data completely., and the ones that are never a duplicate will remain until a duplicate occurs with for example 3-29-G and lets say a 3-29-G occurrs meaning it duplicates it will cancell this data out entirely. so what i mean is if the data occurrs as a duplicate is will be removed. I made a mistake it should placed in Column I 20 the result. sorry :( so the code works in column C but needs to place the non duplicates in column I 20 but also still needs to remove data entirely that was ever a duplicate.

Thank you for you help on this
Sincerely

SamT
12-03-2016, 05:17 PM
:giggles:

Kenneth Hobs
12-03-2016, 05:36 PM
Obviously, it is super easy to change C20 to I20 or whatever cell for the "uniques".

As for removing the "duplicates" from column B, do you want empty cells where "duplicate" values are removed, or the whole row deleted, or the partial removal just in the column and move the column up, or just delete column B data and replace with the unique data?

Once you get that fixed up, you should add data validation so you don't get into that "mess" again.

estatefinds
12-03-2016, 05:49 PM
no i meed macro to run in column C and place the result in the column I. so the Data in column C the macro will remove the dupicates but also the data involved that was a duplicate. so for example the macro would run and find for example 1-1-A and sees that it is a du[licate so it would remove this 1-1-A from the column and once all other duplicates are removed in this fashion it would place this data into column I 20

Kenneth Hobs
12-03-2016, 06:23 PM
I don't know what that means. I guess you want:

just delete column B data and replace with the unique data

Sub AbsUnique2()
Dim r As Range, uv As UniqueValues
Dim c As Range, a, i&

Set r = Range("C20", Range("C20").End(xlDown))
r.FormatConditions.Delete
Set uv = r.FormatConditions.AddUniqueValues
uv.DupeUnique = xlUnique
uv.Interior.ColorIndex = 3 'Red

i = 0
ReDim a(1 To 1)
For Each c In r
If c.DisplayFormat.Interior.ColorIndex <> 3 Then
i = i + 1
ReDim Preserve a(1 To i)
a(i) = c.Value
End If
Next c
r.FormatConditions.Delete 'Remove added format condition

On Error Resume Next
Range("I20").Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
r.ClearContents
Range("C20").Resize(UBound(a)).Value = WorksheetFunction.Transpose(a)
End Sub

estatefinds
12-03-2016, 06:52 PM
im not sure why its coloring red.

but its like if 2 cars exact same show up in a parking lot and they are duplicates , these both cars would not be allowed in the parking lot. now if only one car shows up without duplicates it will be allwed in the parking lot. this goes for the data in my questions.

let me know if you need more info .


thank you, sincerely

Kenneth Hobs
12-03-2016, 07:35 PM
https://www.youtube.com/watch?v=452XjnaHr1A

Apparently, I am not smart enough to figure out what you want. If you want to try again, manually make a sheet before and a sheet after and use maybe 6 cells to illustrate the goals.

Do you see red color in what I did other than the code? If you want to use analogies, one does not need to know how to build a car to be able to drive it. If you must know, I used the conditional format feature to find the duplicates. I thought that was obvious. I then check each cell to see if the conditional format colors are "red" which is what I told it to set for duplicates. If not red, then it is obviously a unique cell. There are many ways to achieve goals. I chose the conditional format method.

estatefinds
12-03-2016, 07:40 PM
ok ill set it up and get back to you, no problem Thank you:)

estatefinds
01-01-2017, 10:22 PM
SORRY i HAVENT GOTTEN BACK TO YOU HAVENT BEEN ON AT ALL. ILL GET BACK TO YOU ON THIS AT LATER. tHANK YOU VERY MUCH!!!

Aussiebear
01-02-2017, 04:18 PM
SORRY i HAVENT GOTTEN BACK TO YOU HAVENT BEEN ON AT ALL. ILL GET BACK TO YOU ON THIS AT LATER. tHANK YOU VERY MUCH!!!You won't be if you keep shouting at us