Compare the words in 2 columns in Excel [Solved/Closed]

Report
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017
-
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
-
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?

12 replies

Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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
chongml
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017

Hi TrowaD

I have done all the steps that u said. But when i double-click the macro i wish to run , nothing happens. Did i missed any necessary steps?
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi Chongml,

Hard to say if you missed anything. I did asume you would run the code from sheet2.

If that is not it, then consider uploading your file (careful with sensitive data) to a free filesharing site like www.speedyshare.com or ge.tt and then post back the download link.

Best regards,
Trowa
chongml
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017
> TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019

I get the result already. Thanks for helping.

But now i gt another problem , like i gt multiple item with same name in different columns , but only the 1st item name that appear will get the letter code , the 2nd one with the same name won't get the letter code to be display. If i wan to get the letter code for all the item with same name , i should make what kinds of changes to my code?

Thanks again for helping.
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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
chongml
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017

woah , u really are the god of excel. U really helped me alot.

Btw , it would be best if u can briefly explain the function of each row of the code do. It will help me to have better understanding.

And one more question , is it possible to compare 2 different files to get the output?

Thanks
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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

chongml
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017

Okay , i also realized that comparing between worksheets is better.

Previously , the code is just comparing 1 column , is it possible to compare multiple column?

For example , column A has 3 types of data , Nike , Adidas and Puma.
PS: Previous column A will move to column B.

If the data in column A of 1st sheet is Adidas, it will run the previous code.
if the data in column A of 1st sheet is Nike , it will run the new command.
If the data in column A of 1st sheet is Puma , it will jump to next cell.

So , what changes should i make to the previous code?

Thanks in advance.
TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
Hi Chongml,

I'm lost as to what you are trying to achieve.

Can you give some sample data as you did with your first post?

Best regards,
Trowa
chongml
Posts
6
Registration date
Tuesday March 28, 2017
Status
Member
Last seen
April 21, 2017

Sorry for making you lost.

For example :
Sheet 1 :
Column1.................Column2................Column3...............
...Type.......................Pin.........................Item..............PriceGroup......
1) SMD .......................2..........................RES0805
2) SMD .......................4..........................IC withSO4
3) THT.........................2..........................RT 2pin -LED
4) THT ........................3...........................3pin-CNN

Sheet 2 :
...Type.......................Pin.........................Item..............PriceGroup......
1) SMD .......................2..........................0805...................a
2) SMD .......................2..........................2010...................b
3) SMD .......................4..........................SO4....................i
4) SMD .......................8..........................SO3....................j
4) THT.........................2..........................LED....................18
5) THT ........................3..........................CNN....................33
5) THT ........................16........................CNN....................34
5) THT ........................100......................CNN....................35

So , i now need a macro to compare Column1 first , then compare the pin , and lastly the item, after that it will fill in the price group.

Result should become like this:
Sheet 1 :
Column1.................Column2................Column3...............
...Type.......................Pin.........................Item..............PriceGroup......
1) SMD .......................2..........................RES0805..............a
2) SMD .......................4..........................IC withSO4............i
3) THT.........................2..........................RT 2pin -LED........18
4) THT ........................3...........................3pin-CNN...............33

Do u have any idea?

Thanks.
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
383
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