asked on

Copy value from a certain cell

Hi Experts!

The scenario is like this, I have a list of items in Column A and Column B that does not match because they were type written and formatted differently, but they are actually the same items. Column A has the correct formatting.

For example, in Column A it is an item is typewritten as 2,4-D Amine 12x1L while in Column B it was type written as 2,4-D Amine 1L, but they are actually the same item. Column A has the correct formatting.

Now, what I want to do is replace the value of Column B with that of the correct format from Column A. I plan to put this first on Column C before fully replacing the values in Column B.

Please help :( thanks!

Ryan Chong

For example, in Column A it is an item is typewritten as 2,4-D Amine 12x1L while in Column B it was type written as 2,4-D Amine 1L, but they are actually the same item

Excel do not have the intelligence to tell whether 2 different values should actually the same value. So, we gonna build the logic (as know as the rules) with the help of formula or macro into the spreadsheet, so that it can tell which values got similarity or get the similarity score etc.

Nikka Castillo

ASKER

HI Sir Ryan Chong,

Thanks for the response sir. I need help with the creation of the formula, I'm not quite knowledgeable with excel formulas. Thank you!

Subodh Tiwari (Neeraj)

See if the following approach works for you.....

Sub ReplaceValues()
Dim alr As Long, blr As Long, cnt As Long
Dim aRng As Range, bRng As Range, aCell As Range, bCell As Range
Dim str() As String
Dim n As Integer, i As Integer
alr = Cells(Rows.Count, 1).End(xlUp).Row
blr = Cells(Rows.Count, 2).End(xlUp).Row
Set aRng = Range("A2:A" & alr)
Set bRng = Range("B2:B" & blr)
For Each bCell In bRng
    If bCell <> "" Then
        MsgBox bCell
        str = Split(bCell, " ")
        n = UBound(str) - 1
        For Each aCell In aRng
            For i = 0 To n
                If InStr(aCell, str(i)) Then cnt = cnt + 1
            Next i
            If cnt = n + 1 Then bCell.Offset(0, 1) = aCell
            cnt = 0
            Exit For
        Next aCell
    End If
Next bCell
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shaun Vermaak

Excellent approach Ryan