PDA

View Full Version : [SOLVED] help with cell values -if same then mark as "duplicate"



Pasi12
01-27-2014, 01:15 PM
Hi everyone,

I ma trying to come up with a simple vb code to chk each column/row for same value and mark them as duplicates in an empty cell/column or any where within worksheet, what is the best way to do this? see attached.
Thanks!
Pasi11167

snb
01-28-2014, 01:04 AM
It's a builtin conditional format (Excel 2010).

Pasi12
01-28-2014, 09:05 AM
Its not doing what I want...

Bob Phillips
01-28-2014, 09:28 AM
What is not doing what you want? In what way is it not doing what you want.

If you want help, you have to put some effort in yourself to explaining what is required.

Pasi12
01-28-2014, 09:43 AM
I did explain what I want at my original post. I tried the "remove duplicates" from the menu but its not working? I selected a cell and it expands with columns and I tried remove duplicates, it says no duplicates found? hope this explains more...

Pasi12
01-28-2014, 09:47 AM
plus I want to mark the ones that are duplicates to show as "duplicates". This is what I amusing for now but its not marking all of my cells , it is selecting randoms and missing few other cells that have duplicates? I have columns A, B,C,D,E.
Thanks!


For Each oneCell In dataRange
If 1 < Application.CountIf(dataRange, oneCell.Value) Then
With oneCell
.Offset(0, 4) = "duplicate"
.EntireRow.Resize(1, .Column + 1).Interior.ColorIndex = 6
End With
End If
Next oneCell

Pasi12
01-28-2014, 10:08 AM
Sorry forgot to paste in all the code:

Sub removeDuplicates()
Dim dataRange As Range, oneCell As Range

With Sheets(2).Range("C:C"): Rem adjust
Set dataRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))

End With


For Each oneCell In dataRange
If 1 < Application.CountIf(dataRange, oneCell.Value) Then
With oneCell
.Offset(0, 4) = "duplicate"
.EntireRow.Resize(1, .Column + 1).Interior.ColorIndex = 6
End With
End If
Next oneCell

Bob Phillips
01-28-2014, 11:18 AM
snb said, albeit somewhat tersely as is his style, that conditional formatting has a duplicates option that you can use to colour highlight the duplicates.

Pasi12
01-28-2014, 11:52 AM
I am aware of this option in excel but I am trying to mark the cells as "duplicate" offset by adjacent cell / or delete them. I want to do this in vba. Thanks.

GTO
01-28-2014, 03:41 PM
I'm not sure what the "extra" column is there for, but without it, maybe like:

In a Standard Module:


Option Explicit

Public Sub example()
Const HEADER_ROW_COUNT As Long = 1
Const FIRST_COLUMN_NUMBER As Long = 1

Dim lLastRow As Long
Dim lCurrentRow As Long

With Sheet1 '<---using CodeName, or using sheet (tab) name ---> ThisWorkbook.Worksheets("MySheet")
lLastRow = Application.Min(.Cells(.Rows.Count, FIRST_COLUMN_NUMBER).End(xlUp).Row, _
.Cells(.Rows.Count, FIRST_COLUMN_NUMBER + 2).End(xlUp).Row _
)
For lCurrentRow = HEADER_ROW_COUNT + 1 To lLastRow
If .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= vbNullString Then

.Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 5).Value = "Duplicate"
Else
.Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 5).ClearContents
End If
Next

End With

End Sub

See attahced workbook, you can adjust offset as needed. Does that help?

Mark

Pasi12
01-28-2014, 04:06 PM
Thank you mark! its start for me I appreciate it! its working fine but trying to understand what you did? also I want to color/highlight those rows that are "duplicate" as well. or Delete those row?
Pasi.

GTO
01-28-2014, 04:45 PM
Thank you mark! its start for me I appreciate it! its working fine but trying to understand what you did? also I want to color/highlight those rows that are "duplicate" as well. OR Delete those row?
Pasi.

Hi Pasi,

I understand that you are wanting to learn how to do it in VBA, and we are happy to help :-) As to that last part however, please pick which one we want to do.

Mark

Pasi12
01-28-2014, 04:48 PM
Sorry, don't mean to make more work for you guys, but I am getting requests on the fly.. :) I should have had it picked but lets do the delete the entire row. I managed to add colors to "Duplicates". Thanks again!

GTO
01-28-2014, 05:06 PM
Try:

Option Explicit

Public Sub example()
Const HEADER_ROW_COUNT As Long = 1
Const FIRST_COLUMN_NUMBER As Long = 1

Dim lLastRow As Long
Dim lCurrentRow As Long

With Sheet1 '<---using CodeName, or using sheet (tab) name ---> ThisWorkbook.Worksheets("MySheet")

'// Find the last row where both 'ID' columns have data. //
lLastRow = Application.Min(.Cells(.Rows.Count, FIRST_COLUMN_NUMBER).End(xlUp).Row, _
.Cells(.Rows.Count, FIRST_COLUMN_NUMBER + 2).End(xlUp).Row _
)

'// Loop from the last row to the first, as we are deleting rows. //
For lCurrentRow = lLastRow To (HEADER_ROW_COUNT + 1) Step -1
'// Test if cells are equal to each other and both have data. //
If .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= vbNullString _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _
= vbNullString Then

.Rows(lCurrentRow).Delete

End If
Next

End With

End Sub

Pasi12
01-28-2014, 05:21 PM
Thank you Sir!

GTO
01-28-2014, 05:32 PM
You are most welcome and glad that worked. Under the <Thread Tools> button at the top of the thread, there is an option avaiable only to you, the OP. You can mark the thread "Solved" (presuming it is). This saved "answerers" from needlessly checking threads.

Thank you so much,

Mark

Pasi12
01-28-2014, 05:52 PM
Done! Thanks again! you guys are great!

Pasi12
01-29-2014, 09:28 AM
Hello Mark,

Thanks so much for helping me yesterday! had a question on the couple of lines in your code what do below lines do?: I am trying to understand your code?

when you get a chance. thanks!:hi:


Const HEADER_ROW_COUNT As Long = 1 XXXX what does this line do?
Const FIRST_COLUMN_NUMBER As Long = 1 XXXX what does this line do?

Also:

For lCurrentRow = lLastRow To (HEADER_ROW_COUNT + 1) Step -1
'// Test if cells are equal to each other and both have data. //
If .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= vbNullString _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _ XXXX what does this line do? " the +2"
= vbNullString Then

.Rows(lCurrentRow).Delete


You are most welcome and glad that worked. Under the <Thread Tools> button at the top of the thread, there is an option avaiable only to you, the OP. You can mark the thread "Solved" (presuming it is). This saved "answerers" from needlessly checking threads.

Thank you so much,

Mark

GTO
01-29-2014, 05:28 PM
Hello Mark,

Thanks so much for helping me yesterday! had a question on the couple of lines in your code what do below lines do?: I am trying to understand your code?

when you get a chance. thanks!:hi:


Const HEADER_ROW_COUNT As Long = 1 XXXX what does this line do?
Const FIRST_COLUMN_NUMBER As Long = 1 XXXX what does this line do?


Hi Pasi,

Happy to help. Some of this you can decipher from the VBA help topics specific to the method, function, etc. As to what those lines do specifically, they simply save the values assigned as constant values. That is, later in the code, you cannot use (for instance)

HEADER_ROW_COUNT=3
...like when you change a variable's value; which as the name implies, the value may vary, whereas a Constant's value is constant (from the moment the code compiles).

Now the reason we use constants is really as simple as this: When writing any lengthy procedure, where you might be using a value a number of times, or, where you may need to pick one of several values to pass as an argument (think in terms of having several options), having some named constants is very handy for you and me. For the snippet of code I wrote for you, I tried giving intuitive names, so that hopefully, it is easy to read later if editing code. In this case, HEADER_ROW_COUNT is simply how many rows are in the "header" and above what cells we want to loop through. Likewise, if your data started in Col A (column 1), and you decided to insert a column in "front" of Col A, then we need to adjust stuff to now run up Col B. With a constant, we can just change the value (in design-time) of FIRST_COLUMN_NUMBER to 2, and presto, we don't have to change a number in several places in the remaining code. You'll notice that if we had used the number 1 instead of the constant, and we needed to adjust it to 2, we'd need to change this is four places even in this short bit of code. Does that make sense as to the advantage?

Finally, I mentioned using one of several constants as "options" which are easier and more intuitive for us. An easy example would be in using API functions. For this conversation, a function need not be studied, just think of the concept. The API function 'ShowWindow', does just that. It changes how a window is shown, just like we do when we click the minimize, restore/maximize buttons. (ShowWindow can also hide a window, but just ignore that for this example.)

Four commonly used constants for ShowWindow() nCmdShow parameter are:


Const SW_HIDE = 0
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWMAXIMIZED = 3


It is much easier to use one of the named constants, as the constant's name tells us what it will do.



Also:

For lCurrentRow = lLastRow To (HEADER_ROW_COUNT + 1) Step -1
'// Test if cells are equal to each other and both have data. //
If .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER).Value _
= vbNullString _
And Not .Cells(lCurrentRow, FIRST_COLUMN_NUMBER + 2).Value _ XXXX what does this line do? " the +2"
= vbNullString Then

.Rows(lCurrentRow).Delete

Look in VBA Help for the .Cells property. The second arg is for column, so we are just checking two columns over from the first, and overall, ensuring that we are not determining duplication because both cells happen to be empty.

Hope that helps,

Mark

Pasi12
01-29-2014, 05:59 PM
Thank you Mark as always! You Rock!!