VBA / Find Dublicate Value's last row.

[burakorhan23]

Hello mrexcel family,

I'm new one for this forum and I want to need your help. I want to find dublicate value's last rows and unique values then I want to write "use this row" second row with macro code. After that I will use this code in some userform design.

For Example Data is :

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]10


[/TD]
[/TR]
</tbody>[/TABLE]
After Macro using data should be :
[TABLE="width: 193"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]use this row[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]use this row[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

you can do this with a formula:

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIFS(<font color="Red">A$1:A1,A1</font>)=COUNTIFS(<font color="Red">$A$1:$A$12,A1</font>),"use this row",""</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013/2016
	A	B
1	1
2	2
3	3
4	4	use this row
5	5	use this row
6	2
7	2	use this row
8	3	use this row
9	6	use this row
10	7	use this row
11	1	use this row
12	10	use this row
Sheet3

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Apr00
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    .Item(K).Offset(, 1) = "use this row"
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[burakorhan23]

Thanks for VBA code. This is reaaly useful for my design

 

[Rick Rothstein]

Thanks for VBA code. This is reaaly useful for my design

Here is another macro (more compact, no loops) that you can also consider...

[table="width: 500"]
[tr]
	[td]Sub MarkLastValues()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1).Formula = "=IF(COUNTIFS(A$1:A1,A1)=COUNTIFS(" & .Address & ",A1),""use this row"","""")"
    .Offset(, 1).Value = .Offset(, 1).Value
  End With
End Sub[/td]
[/tr]
[/table]