Remove Duplicates in Second Column, but refresh if new header in First Column

[WPitt]

Hi all,

I've got a list of data in column A that has duplicates clustered together. Column A could have duplicates, but after the cluster of duplicates, I know the values to be unique. Column B has duplicates, that are repeated, and need to be visible if the data in column A changes. I'd like to create blanks where repeated, but column B needs to be able to repeat single use items.


A B
Red 1
Red 2
Red 3
Red 4
Green 1
Green 2
Green 3
Yellow 1
Yellow 2





Ideal scenario:
A B
Red 1
2
3
4
Green 1
2
3
Yellow 1
2



I tried using this IF statement, which works well for column A, but eliminates the duplicate instances in column B, which I need to exist if the data in column A changes.
=IF(COUNTIF($A$2:A2,A2)>1,"",A2)

Any help would be much appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this macro in a copy of your workbook.

Sub RemoveDupesColA()
  Application.ScreenUpdating = False
  With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(A2=A1,1,"""")"
    On Error Resume Next
    .SpecialCells(xlFormulas, xlNumbers).Offset(, -2).ClearContents
    On Error GoTo 0
    .ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[WPitt]

Thanks MrExcelMVP! I was able to add your code as a module in my workbook. I added 3 other instances of the code, and modified the "With Range(C2:C" portion to run in additional columns. Using an order of operations, I was able to produce the columns necessary, then paste them into a new worksheet and merge the columns together. Appreciate the help!

 

[Peter_SSs]

Cheers. Glad you got what you wanted in the end.