VBA- Delete Values in Column B if Duplicates found in Coulmn A

[anna99]

Hi All,

My table have multiple columns.
BEFORE

Col1	Col2	Col3
AA	100​
BB	200​
CC	300​
AA	100​
BB	200​
BB	200​
AA	100​
DD	400​
EE	500​
FF	600​
CC	300​
GG	700​
HH	800​

AFTER

Col1	Col2	Col3
AA	100​
BB	200​
CC	300​
AA	​
BB	​
BB	​
AA	​
DD	400​
EE	500​
FF	600​
CC
GG	700​
HH	800​

 

[JoeMo]

Assuming your values in col 1 & col 2 are constants, not returned by formulas, try this after you change the range to suit your sheet.

Sub DeleteDupValues()
'assumes no formulas in the range R
Dim R As Range, Vin As Variant, i As Long, j As Long
Set R = Range("A2:B14") 'change range to suit
Vin = R.Value
For i = LBound(Vin, 1) To UBound(Vin, 1) - 1
    For j = i + 1 To UBound(Vin, 1)
        If Vin(j, 1) = Vin(i, 1) Then Vin(j, 2) = ""
    Next j
Next i
R.Value = Vin
End Sub

 

[anna99]

Assuming your values in col 1 & col 2 are constants, not returned by formulas, try this after you change the range to suit your sheet.

Sub DeleteDupValues()
'assumes no formulas in the range R
Dim R As Range, Vin As Variant, i As Long, j As Long
Set R = Range("A2:B14") 'change range to suit
Vin = R.Value
For i = LBound(Vin, 1) To UBound(Vin, 1) - 1
    For j = i + 1 To UBound(Vin, 1)
        If Vin(j, 1) = Vin(i, 1) Then Vin(j, 2) = ""
    Next j
Next i
R.Value = Vin
End Sub

Thank you so much Joe, unfortunately i have formula in column B, is there anyway that we can make it work when formula is there. thank you

 

[Fluff]

Do you want to remove the formula from col B where col A is a duplicate?

Also does this need to work on both Mac & Windows?

 

[anna99]

Do you want to remove the formula from col B where col A is a duplicate?

Also does this need to work on both Mac & Windows?

Hi Fluff, thanks for asking. I run the code that JoeMo provided, it works well. the only issue is, column B has formulas, when i run the code above it will remove my formulas in column B. my purpose is only keep 1 value in column B if duplicates found in column A. as table shown on my thread. hope it's clear

 

[Fluff]

That doesn't answer either of my questions.

 

[anna99]

That doesn't answer either of my questions.

Sorry,
Do you want to remove the formula from col B where col A is a duplicate? --> no i don't want to remove the formula in column B

Also does this need to work on both Mac & Windows? -> no, only Windows is good

thank you

 

[Fluff]

If you want to keep the formula, then you cannot delete the value in col B.
You would need to change the formula so that it only returns a result for the first instance of the value in col A

 

[anna99]

If you want to keep the formula, then you cannot delete the value in col B.
You would need to change the formula so that it only returns a result for the first instance of the value in col A

Thank you!

 

[Fluff]

You're welcome.