Macro to find a duplicate row and add separator

sncb

Board Regular
Joined
Mar 17, 2011
Messages
168
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi All,

If anyone could assist, it would be great. I need the VB code to look for a duplicate record in the rows above and if found then add a value to the first cell, something like a "2" such that it separates itself from the first one:

Eg:

If such a situation arises:

Excel Workbook
ABC
1PO TypePO NumberType/Number
2AB1234AB1234
3BC1234BC1234
4AB1234AB1234
Sheet1



then edit it to:


Excel Workbook
ABC
1PO TypePO NumberType/Number
2AB1234AB1234
3BC1234BC1234
4AB21234AB21234
Sheet1



Thanks All
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Try

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> AddSuffix()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns("B").Insert<br>    <SPAN style="color:#00007F">With</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>        .Offset(, 1).FormulaR1C1 = "=RC[-1]&IF(COUNTIF(R2C[-1]:" _<br>            & "RC[-1],RC[-1])>1,COUNTIF(R2C[-1]:RC[-1],RC[-1]),"""")"<br>        .Value = .Offset(, 1).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Columns("B").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:
Upvote 0
Thanks Peter,

Bingo. Worked like a charm.

Just another thing though. Can I specify to perform this action on a particular tab since I have 4 other tabs as well. This tab is called 'Hold'

B Rgds
 
Upvote 0
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> AddSuffix()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Hold")<br>        .Columns("B").Insert<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A2", .Range("A" & .Rows.Count).End(xlUp))<br>            .Offset(, 1).FormulaR1C1 = "=RC[-1]&IF(COUNTIF(R2C[-1]:" _<br>                & "RC[-1],RC[-1])>1,COUNTIF(R2C[-1]:RC[-1],RC[-1]),"""")"<br>            .Value = .Offset(, 1).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .Columns("B").Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top