Requested for macro

J

jackgn

New Member
Joined
May 20, 2011
Messages
17
Hi All,
I have some data in sheet1 and some data in sheet2. I want to see if the values in sheet2 is present in sheet1 and print them in sheet 3 if present. I dont know how to do it. This has to be done repeatedly and would be happy if i get a macro for this, so that i can just run the macro whenever i need to do the comaprison..
Thanks in advance for the help...:-)
Regards,
Jack
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
jackgn,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net
 
Upvote 0
jackgn,


Sample raw data in worksheet Sheet1 (not all of the 146 rows are shown for brevity):


Excel Workbook
A
1command: E\:\Autosys_Jobs\IL\N014.bat
2command: E\:\Autosys_Jobs\IL\N009.bat
3command: E\:\Autosys_Jobs\IL\N010.bat
4command: E\:\Autosys_Jobs\IL\S117.bat
5command: E\:\Autosys_Jobs\IL\S109.bat
6command: E\:\Autosys_Jobs\IL\J170.bat
7command: E\:\Autosys_Jobs\IL\J171.bat
8command: E\:\Autosys_Jobs\IL\J052.bat
9command: E\:\Autosys_Jobs\IL\J054.bat
10command: E\:\Autosys_Jobs\IL\J057.bat
11command: E\:\Autosys_Jobs\IL\J076.bat
12command: E\:\Autosys_Jobs\IL\J087.bat
13command: E\:\Autosys_Jobs\IL\J100.bat
14command: E\:\Autosys_Jobs\IL\J172.bat
15command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_DatatoStoreline.vbs"
16command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_C026_Supplier.vbs"
17command: Cscript "G\:\Autosys_Jobs\RESA\Scripts\TOM.SSIS.SalesAndCashOfficeExtract.vbs"
18command: E\:\Autosys\Jobs\bat\TOMFileUploadToORBaseProduct.bat
Sheet1





Sample raw data in worksheet Sheet2:


Excel Workbook
A
1command: E\:\Autosys_Jobs\IL\J054.bat
2command: E\:\Autosys_Jobs\IL\J057.bat
3command: E\:\Autosys_Jobs\IL\J076.bat
4command: E\:\Autosys_Jobs\IL\J087.bat
5command: E\:\Autosys_Jobs\IL\J100.bat
6command: E\:\Autosys_Jobs\IL\J172.bat
7command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_DatatoStoreline.vbs"
8command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_C026_Supplier.vbs"
9command: E\:\Autosys\Jobs\bat\TOMFileUploadToORProductTradingPercentages.bat
10command: E\:\Autosys\Jobs\bat\TOMFileUploadToORSimplerDiariesReport.bat
11command: E\:\Autosys_Jobs\IL\S100.bat
12command: E\:\Autosys_Jobs\IL\S008.bat
13command: E\:\Autosys_Jobs\IL\S061.bat
14command: E\:\Autosys_Jobs\IL\S009.bat
15command: E\:\Autosys_Jobs\IL\S001.bat
16command: E\:\Autosys_Jobs\IL\S0525.bat
17command: E\:\Autosys_Jobs\IL\S022.bat
18command: E\:\Autosys_Jobs\IL\N003A.bat
19command: E\:\Autosys_Jobs\IL\S110A.bat
20command: E\:\Autobs\IL\S110A.bat
21command: E\:\Autobs110A.bat
22
Sheet2





Worksheet Sheet3 before the macro:


Excel Workbook
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet3





Worksheet Sheet3 after the macro:


Excel Workbook
A
1command: E\:\Autosys_Jobs\IL\J054.bat
2command: E\:\Autosys_Jobs\IL\J057.bat
3command: E\:\Autosys_Jobs\IL\J076.bat
4command: E\:\Autosys_Jobs\IL\J087.bat
5command: E\:\Autosys_Jobs\IL\J100.bat
6command: E\:\Autosys_Jobs\IL\J172.bat
7command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_DatatoStoreline.vbs"
8command: Cscript "G\:\Autosys_Jobs\Storeline\Scripts\Ref_C026_Supplier.vbs"
9command: E\:\Autosys\Jobs\bat\TOMFileUploadToORProductTradingPercentages.bat
10command: E\:\Autosys\Jobs\bat\TOMFileUploadToORSimplerDiariesReport.bat
11command: E\:\Autosys_Jobs\IL\S100.bat
12command: E\:\Autosys_Jobs\IL\S008.bat
13command: E\:\Autosys_Jobs\IL\S061.bat
14command: E\:\Autosys_Jobs\IL\S009.bat
15command: E\:\Autosys_Jobs\IL\S001.bat
16command: E\:\Autosys_Jobs\IL\S0525.bat
17command: E\:\Autosys_Jobs\IL\S022.bat
18command: E\:\Autosys_Jobs\IL\N003A.bat
19command: E\:\Autosys_Jobs\IL\S110A.bat
20
Sheet3





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub Comparew2w1()
' hiker95, 05/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=551673
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim c As Range, FR As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
Set w3 = Worksheets("Sheet3")
w3.Columns(1).ClearContents
NR = 0
For Each c In w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(1), 0)
  On Error GoTo 0
  If FR > 0 Then
    NR = NR + 1
    w3.Cells(NR, 1).Value = c
  End If
Next c
w3.Columns(1).AutoFit
w3.Activate
End Sub


Then run the Comparew2w1 macro.
 
Upvote 0
jackgn,


Same screenshots as my last reply, but the macro will run much faster because of the use of three arrays.



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Option Base 1
Sub CompareW2W1()
' hiker95, 05/20/2011
' http://www.mrexcel.com/forum/showthread.php?t=551673
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim w1ary As Variant, w2ary As Variant, w3ary As Variant
Dim a As Long, b As Long, f As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
Set w3 = Worksheets("Sheet3")
w3.Columns(1).ClearContents
w1ary = w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
w2ary = w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp))
ReDim w3ary(1 To UBound(w2ary), 1 To 1)
b = 0
For a = LBound(w2ary) To UBound(w2ary)
  f = 0
  On Error Resume Next
  f = Application.Match(w2ary(a, 1), w1ary, 0)
  On Error GoTo 0
  If f > 0 Then
    b = b + 1
    w3ary(b, 1) = w2ary(a, 1)
  End If
Next a
w3.Range("A1").Resize(UBound(w3ary)).Value = w3ary
w3.Columns(1).AutoFit
w3.Activate
Application.ScreenUpdating = True
End Sub


Then run the CompareW2W1 macro.
 
Upvote 0
Hi all,

This macro works fine thx for all, small change i would reqired in the result, the above macro display the value which is present in sheet1 and sheet2 the result will pull out the matching value to sheet3. the new result should be compare the value from sheet1 and the value which is not present in sheet2 those value should pull out as result in sheet3, can any one help me with this macro thanks in advance
Regards,
Jackgn
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Automated Reporting - From Excel to PPT
Replies
1
Views
56
Rijnsent
Rijnsent
D
  • Question Question
Macro script and scheduler
Replies
1
Views
294
Micron
M
M
  • Question Question
Ignore part of word in search
Replies
1
Views
98
Micron
M
S
  • Solved
Button to print to PDF with custom file name & custom location
Replies
3
Views
622
mumps
mumps
A
  • Question Question
Macro VBA hide/unhide with veryHidden option and VBA password
Replies
1
Views
691
alexbw
A

Forum statistics

Threads
1,224,521
Messages
6,179,283
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top