Macro to find a duplicate row and add separator

[sncb]

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
	A	B	C
1	PO Type	PO Number	Type/Number
2	AB	1234	AB1234
3	BC	1234	BC1234
4	AB	1234	AB1234
Sheet1

then edit it to:

Excel Workbook
	A	B	C
1	PO Type	PO Number	Type/Number
2	AB	1234	AB1234
3	BC	1234	BC1234
4	AB2	1234	AB21234
Sheet1

Thanks All

 

[Peter_SSs]

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>

 

[sncb]

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

 

[Peter_SSs]

<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>

 

[sncb]

It worked Peter.

Thank you very much.