Related:
- Compare the words in 2 columns in Excel
- A variation on the compare 2 columns in Excel ✓ - Forum - Excel
- How to compare one cell with entire column in excel - How-To - Excel
- Find Value Between 2 column Range Excel Macro ✓ - Forum - Excel
- Matching 2 columns in Excel ✓ - Forum - Excel
- Intersection of 2 columns in excel - Forum - Excel
4 replies
TrowaD
- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021
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:
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
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
- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021
Hi Chongml,
For that use the following code:
Best regards,
Trowa
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
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
TrowaD
- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021
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
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.
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.
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.
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.
TrowaD
- Posts
- 2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021
Hi Chongml,
That is a lot more clear.
Let this code do it's work:
Best regards,
Trowa
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
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
-
2829
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- November 22, 2021
490Hard 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.