Turn duplicate rows into unique VBA

Panoos64 · Nov 14, 2018

Hi all, i would like to turn duplicate rows into unique using VBA. Therefore the data start from "A2" and the code should run through col. "A" and where the rows are duplicates, should place a number (serial number) on left of description. below i present the original data and the expected result. Thank you all in advance

Original data

	[FONT=&quot]A[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]Description[/FONT]
[FONT=&quot]2[/FONT]	[FONT=&quot]Social Insurance[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]Provident Fund [/FONT]
[FONT=&quot]4[/FONT]	[FONT=&quot]Medical Fund [/FONT]
[FONT=&quot]5[/FONT]	[FONT=&quot]Income Tax[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]Trade Union Contrib.[/FONT]
[FONT=&quot]7[/FONT]	[FONT=&quot]Social Insurance[/FONT]
[FONT=&quot]8[/FONT]	[FONT=&quot]Provident Fund [/FONT]
[FONT=&quot]9[/FONT]	[FONT=&quot]Medical Fund [/FONT]
[FONT=&quot]10[/FONT]	[FONT=&quot]Income Tax[/FONT]
[FONT=&quot]11[/FONT]	[FONT=&quot]Trade Union Contrib.[/FONT]

Expected result

	[FONT=&quot]A[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]Description[/FONT]
[FONT=&quot]2[/FONT]	[FONT=&quot]1 Social Insurance[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]2 Provident Fund [/FONT]
[FONT=&quot]4[/FONT]	[FONT=&quot]3 Medical Fund [/FONT]
[FONT=&quot]5[/FONT]	[FONT=&quot]4 Income Tax[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]5 Trade Union Contrib.[/FONT]
[FONT=&quot]7[/FONT]	[FONT=&quot]6 Social Insurance[/FONT]
[FONT=&quot]8[/FONT]	[FONT=&quot]7 Provident Fund [/FONT]
[FONT=&quot]9[/FONT]	[FONT=&quot]8 Medical Fund [/FONT]
[FONT=&quot]10[/FONT]	[FONT=&quot]9 Income Tax[/FONT]
[FONT=&quot]11[/FONT]	[FONT=&quot]10 Trade Union Contrib.[/FONT]

James006 · Nov 14, 2018

Hello,

Why don't you use from the Menu ... Remove Duplicates ...?

Panoos64 · Nov 14, 2018

Hi James, some of duplicates rows i need them and so i would like to make all of them unique so that to choose the useful and then to delete the unneeded rows. I don't know how to make duplicate or triplicate rows to unique. Therefore that column "A" contains triplicate and not duplicate descriptions as i presented on my post. Just i need the command to place the serial number on left side of description. Thank you for your interesting to resolve my issue.

Panoos64 · Nov 14, 2018

Apologies, serial number for each group of duplicate, triplicate rows. e.g. if Social Insurance is triplicate should place 1,2,3. respectively or e.g. Medical Fund is duplicate should place 1,2, respectively too.

Thanks

MickG · Nov 14, 2018

Perhaps this:-

Code:

[COLOR="Navy"]Sub[/COLOR] MG14Nov09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    Dn.Value = c & " " & Dn.Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Panoos64 · Nov 14, 2018

Hi Mick, it works perfect base on my first post, but unfortunately i done a mistake presenting my data, i wanted a s.n. for each section of duplicate, or triplicate, quadruplicate and so on, instead of s.n. for each description / row. Kindly see below. I express my apologies for any inconvenience i caused. I would like to thank you once again for your continuance support. Hv a great day!

	[FONT=&quot]A[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]Description[/FONT]
[FONT=&quot]2[/FONT]	[FONT=&quot]Social Insurance[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]Provident Fund [/FONT]
[FONT=&quot]4[/FONT]	[FONT=&quot]Medical Fund [/FONT]
[FONT=&quot]5[/FONT]	[FONT=&quot]Social Insurance[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]Provident Fund [/FONT]
[FONT=&quot]7[/FONT]	[FONT=&quot]Medical Fund [/FONT]
[FONT=&quot]8[/FONT]	[FONT=&quot]Social Insurance[/FONT]
[FONT=&quot]9[/FONT]	[FONT=&quot]Provident Fund [/FONT]

	[FONT=&quot]A[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]Description[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]1 Social Insurance[/FONT]
[FONT=&quot]4[/FONT]	[FONT=&quot]1 Provident Fund [/FONT]
[FONT=&quot]5[/FONT]	[FONT=&quot]1 Medical Fund [/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]2 Social Insurance[/FONT]
[FONT=&quot]7[/FONT]	[FONT=&quot]2 Provident Fund [/FONT]
[FONT=&quot]8[/FONT]	[FONT=&quot]2 Medical Fund [/FONT]
[FONT=&quot]9[/FONT]	[FONT=&quot]3 Social Insurance[/FONT]
[FONT=&quot]10[/FONT]	[FONT=&quot]3 Provident Fund [/FONT]

Fluff · Nov 14, 2018

How about

Code:

Sub AddSerial()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp))
   With CreateObject("scripting.dictionary")
      For i = 2 To UBound(Ary)
         .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + 1
         Ary(i, 1) = .Item(Ary(i, 1)) & " " & Ary(i, 1)
       Next i
      Range("A1").Resize(UBound(Ary)).Value = Application.Index(Ary, , 1)
   End With
End Sub

Panoos64 · Nov 14, 2018

Perfect work Fluff! Many thanks for your time spent for my project. Hv a lovely day!

Fluff · Nov 14, 2018

Glad we could help & thanks for the feedback

Turn duplicate rows into unique VBA

Panoos64

Well-known Member

Excel Facts

James006

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

MickG

MrExcel MVP

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Turn duplicate rows into unique VBA

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock