Compare the words in 2 columns in ExcelSolved/Closed

Question

I gt two different excel file . So now my task is to compare the words in the entire columns in excel file 1 and excel file 2 , then output a new column.

For example:
excel file 1:
Item                         Price Group
Svc0805
R9k
JJPC
BGA77

Excel file 2:
Item Description         Price Group
0805                                 A
9987                                 B
9k                                    C
DSK                                  D
JJPC                                  AF
VR                                    E9
BGA                                  SS

So , what i looking for the answer is like this :
the excel file1 will update when i run the script/ macro
Item                         Price Group
Svc0805                             A
R9k                                   C
JJPC                                  AF
BGA77                               SS

Anyone know how to write the code for this situation?

TrowaD · Answer

Hi Chongml,

Put both sheets in 1 file.
Sheet1 has the complete item names in column A.
Sheet2 has part of the item names in column A and the letter codes in column B.

The following code will put the letter code from sheet2 in sheet1 next to its respective item name in column B:
Sub RunMe()
Dim mFind As Range

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet1").Columns("A:A").Find(what:=cell.Value, lookat:=xlPart)
    If mFind Is Nothing Then GoTo NextCell
    mFind.Offset(0, 1).Value = cell.Offset(0, 1).Value
NextCell:
Next cell

End Sub

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa

TrowaD · Answer

Hi Chongml,

For that use the following code:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet2").Select
For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet1").Columns("A:A").Find(what:=cell.Value, lookat:=xlPart)
    If mFind Is Nothing Then GoTo NextCell
    firstAddress = mFind.Address
    Do
        mFind.Offset(0, 1).Value = cell.Offset(0, 1).Value
        Set mFind = Sheets("Sheet1").Columns("A:A").FindNext(mFind)
    Loop While mFind.Address <> firstAddress
NextCell:
Next cell

End Sub

Best regards,
Trowa

TrowaD · Answer

Glad to be able to help you out Chongml.


Here is my attempt to explain the code:
1. Macro name
2. Declare mFind as range
4. Select sheet2
5. Start For Each loop in range A2:A and last row used. Each cell in the range is refered to as 'cell'
6. Find cell value in sheet1 column A and link it to mFind
8. When nothing is found goto code line 14.
9. Remember the first range found.
10. Start Do loop
11. 1 cell to the right of mFind = 1 cell to the right of 'cell' 
12. Find the next value in sheet1 column A and link it to mFind
13. Keep looping until mFind is the same as the first range found
15. Goto next 'cell' in the For Each loop

As for the comparing 2 files. Personally I try to avoid it as much as possible, since Excel has the option to have multiple sheets for a reason; to keep all relevant data in one place.
But the same way you set a range, you can set a workbook as well:
Set wbk = Workbooks.Open("C:\myworkbook.xls")

Then use that in your cell reference:
Workbook.Sheet.Cell
wbk.Sheets("Sheet1").range("A1").

Best regards,
Trowa

TrowaD · Answer

Hi Chongml,

That is a lot more clear.

Let this code do it's work:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet2").Select
For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet1").Columns("C:C").Find(what:=cell.Value, lookat:=xlPart)
    If mFind Is Nothing Then GoTo NextCell
    firstAddress = mFind.Address
    Do
        If mFind.Offset(0, -1).Value = cell.Offset(0, -1).Value And _
        mFind.Offset(0, -2).Value = cell.Offset(0, -2).Value Then
            mFind.Offset(0, 1).Value = cell.Offset(0, 1).Value
        End If
        Set mFind = Sheets("Sheet1").Columns("C:C").FindNext(mFind)
    Loop While mFind.Address <> firstAddress
NextCell:
Next cell

End Sub
Best regards,
Trowa

Compare the words in 2 columns in Excel

4 replies

Subscribe To Our Newsletter!