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