Previous Topic Next Topic

Compare the words in 2 columns in Excel

Solved/Closed
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017 - Updated on Jun 16, 2017 at 02:44 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 24, 2017 at 11:34 AM
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?
Related:

4 responses

Answer 1 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 28, 2017 at 11:48 AM
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
0
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017
Mar 28, 2017 at 08:43 PM
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?
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 30, 2017 at 11:01 AM
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
0
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Apr 4, 2017 at 04:44 AM
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.
0
Answer 2 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 4, 2017 at 11:56 AM
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
0
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017
Updated on Apr 4, 2017 at 09:15 PM
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
0
Answer 3 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 18, 2017 at 11:16 AM
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

0
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017
Apr 19, 2017 at 01:32 AM
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 20, 2017 at 10:41 AM
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
0
chongml Posts 6 Registration date Tuesday March 28, 2017 Status Member Last seen April 21, 2017
Apr 21, 2017 at 01:57 AM
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.
0
Answer 4 / 4
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Apr 24, 2017 at 11:34 AM
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
0