compare columns in two different sheets.

G

Gangster

New Member
Joined
Apr 15, 2014
Messages
23
HELLO GURUS,
im very new to VBA macro. i have a task to be done. i have two sheets in same workbook.
In each sheet i have a 20*8 matrix table with data.
i need to compare column B and C from sheet1 to all the values in column B and C in sheet.
for ex: sheet1
B(2,2) and C(2,3) to any rows in column B and C.
if they match i need to retrieve data from column say D to H from sheet1 and place it in sheet2.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Gangster,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?

Can you post a screenshot of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel.com | Excel Resources | Excel Seminars | Excel Products


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Last edited:
Upvote 0
hi hiker95,
Thanks for the quick reply..!!
1. im using excel 2007
2. Microsoft Windows
-
d22ip8x8qaslk7w2kucd


https://app.box.com/s/d22ip8x8qaslk7w2kucd

if the cells match..then sheet2 will look like sheet1. matched data wil b moved to sheet2.
how do i upload a image to this post ?.. its asking for link..
 
Last edited:
Upvote 0
Gangster,

Thanks for the workbook.

I am using column N in both worksheets as a work area.


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub UpdateSheet2()
' hiker95, 04/16/2014, ME771379
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, nrng As Range
Dim lr1 As Long, lr2 As Long
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
With w2
  lr2 = .Cells(Rows.Count, "B").End(xlUp).Row
  With .Range("N2:N" & lr2)
    .FormulaR1C1 = "=RC[-12]&RC[-11]"
    .Value = .Value
  End With
End With
With w1
  lr1 = .Cells(Rows.Count, "B").End(xlUp).Row
  With .Range("N2:N" & lr1)
    .FormulaR1C1 = "=RC[-12]&RC[-11]"
    .Value = .Value
  End With
  For Each c In .Range("N2:N" & lr1)
    If c <> "" Then
      Set nrng = w2.Columns("N:N").Find(c, LookAt:=xlWhole)
      If Not nrng Is Nothing Then
        .Range("H" & c.Row).Resize(, 6).Copy w2.Range("H" & nrng.Row)
        Application.CutCopyMode = False
      End If
    End If
  Next c
End With
w1.Range("N2:N" & lr1).ClearContents
w2.Range("N2:N" & lr2).ClearContents
With w2
  .Columns("H:M").AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateSheet2 macro.
 
Upvote 0
hi hiker95,
Its working...Thanks alot :)
I just have couple of questions.

1. can u plz insert comment lines..so that i can understand the working of the code. as im just a beginner.
2. is it possible to display the source machine along with its volume & disk space free% which doesn't matches in a separate msgbox or anywhere in worksheet2.
 
Upvote 0
Gangster,

Thanks for the feedback.

You are very welcome. Glad I could help.


1. can u plz insert comment lines..so that i can understand the working of the code. as im just a beginner.
Click to expand...

I can do that - be back later.


2. is it possible to display the source machine along with its volume & disk space free% which doesn't matches in a separate msgbox or anywhere in worksheet2.
Click to expand...

I do not understand?

I will have to see three worksheets:
1. Sheet1
2. Sheet2, before the macro
3. Sheet3, after the macro (manually formatted by you) for the new results you are looking for.
 
Upvote 0
Gangster,

With worksheets Sheet1, and, Sheet2 containing raw data per your latest workbook. And, Sheet3 containing titles.

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).

Code: 
Sub UpdateSheet2Sheet3()
' hiker95, 04/18/2014, ME771379
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim c As Range, nrng As Range
Dim lr1 As Long, lr2 As Long, lr3 As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
Set w3 = Sheets("Sheet3")
With w3
  lr3 = .Cells(Rows.Count, "A").End(xlUp).Row
  If lr3 > 1 Then .Range("A2:G" & lr3).ClearContents
End With
With w2
  lr2 = .Cells(Rows.Count, "B").End(xlUp).Row
  With .Range("N2:N" & lr2)
    .FormulaR1C1 = "=RC[-12]&RC[-11]"
    .Value = .Value
  End With
End With
With w1
  lr1 = .Cells(Rows.Count, "B").End(xlUp).Row
  With .Range("N2:N" & lr1)
    .FormulaR1C1 = "=RC[-12]&RC[-11]"
    .Value = .Value
  End With
  For Each c In .Range("N2:N" & lr1)
    If c <> "" Then
      Set nrng = w2.Columns("N:N").Find(c, LookAt:=xlWhole)
      If Not nrng Is Nothing Then
        .Range("H" & c.Row).Resize(, 6).Copy w2.Range("H" & nrng.Row)
        Application.CutCopyMode = False
      End If
    End If
  Next c
End With
w1.Range("N2:N" & lr1).ClearContents
w2.Range("N2:N" & lr2).ClearContents
With w2
  For r = 2 To lr2
    If .Cells(r, 8) = "" Then
      nr = w3.Cells(w3.Rows.Count, "A").End(xlUp).Row + 1
      .Range("A" & r & ":G" & r).Copy w3.Range("A" & nr)
      Application.CutCopyMode = False
    End If
  Next r
  .Columns("H:M").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateSheet2Sheet3 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
106
MountainFog
M
E
  • Solved
Find, lookup, match - based on 2 separate criteria.
Replies
4
Views
128
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
102
JMC57
J
B
  • Question
Getting error if linked cell row deleted in different sheet of large data.
Replies
3
Views
109
Peter_SSs
Peter_SSs
A
  • Question
How do you compare two load sheets to tell you exactly what the differences are
Replies
5
Views
249
AhoyNC
A

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

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