Making Value common in corresponding Cell

[kshitij_dch]

I have 2 columns

Column A and Column B
Column B is sorted and It have common Value group together

Column A has unique values corresponding to column B

I want a formula or a macro which will find a group of common value in B column and Make first value common for group in column B for an example :

It is like :-

ID's	Loan Number
123	ABC
456	ABC
789	ABC
4545	BCA
5656	BCA
6767	BCA

I want like -:

ID's	Loan Number
123	ABC
123	ABC
123	ABC
4545	BCA
4545	BCA
4545	BCA

 

[Legacy 143009]

Sub test()
  Dim lRow As Long, f As Long
  lRow = Cells(Rows.Count, 1).End(xlUp).Row

  For i = 2 to lRow
    If Cells(i, 2).Value <> Cells(i-1, 2).Value Then
      For j = 1 to lRow
        If Cells(i + j, 2).Value = Cells(i, 2).Value Then
          Cells(i + j, 1).Value = Cells(i, 1).Value
        Else
          Exit For
        End If
      Next
    End If
  Next
End Sub

 

[shinigamilight]

Sub test()
            Dim lr As Long
            
            lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlWhole, xlByRows, xlPrevious, False).Row
            Dim k As Integer
            
            For k = 2 To lr
                If Range("B" & k) = Range("B" & k + 1) Then
                    Range("A" & k + 1) = Range("A" & k)
                End If
            Next k

End Sub

 

[Peter_SSs]

A non-looping alternative that you could test:

Sub CommonValue()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("if(" & .Offset(, 1).Address & "=" & .Offset(-1, 1).Address & ",""""," & .Address & ")")
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub