MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

compare data from different worksheets

  Asked By: Koila    Date: Aug 18    Category: MS Office    Views: 976

I need help on compare data from different worksheets

For clarity, I have placed the sample datasets at the following URL

Given: the datasets for sheet1 and 2 might not contain the same number
of rows.

I am looking for new rows (it might be inserted in any position of the
rows) in sheet1. The new rows will be highlighted

in red color. For example:

Your time and assistance is greatly appreciated.



4 Answers Found

Answer #1    Answered By: Fairuzah Alam     Answered On: Aug 18

We guess you want to compare  line numbers.
if a line number does not appear in sheet2, color  it red.
Try this

Sub m()
Dim mws As Worksheet
Dim cws As Worksheet

Dim mrng As Range
Dim crng As Range
Dim c As Range

Dim awf As WorksheetFunction
Set awf = Application.WorksheetFunction

Set mws = Sheets(1)
Set mrng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))

Set cws = Sheets(2)
Set crng = Range(Cells(2, 2), Cells(2, 2).End(xlDown))

For Each c In mrng
If awf.CountIf(crng, c) <> 1 Then mws.Cells(c.Row, 1).Resize
(1, 6).Interior.Color = vbRed
Next c

End Sub

Answer #2    Answered By: Gerardo Morgan     Answered On: Aug 18

We clarified our own misunderstanding and rewrite a few line thus

> Set mws = Sheets(1)
> Set mrng = Range(mws.Cells(2, 2), mws.Cells(2, 2).End(xlDown))
> Set cws = Sheets(2)
> Set crng = Range(cws.Cells(2, 2), cws.Cells(2, 2).End(xlDown))

Answer #3    Answered By: Kawakib Mansour     Answered On: Aug 18

The comparison need to depend on column A,B,C,D and F. This is because the same
id of stock might have different linenumber, Shipnumber, Release or person

So I need to combine all these columns as the condition to compare.

Answer #4    Answered By: Julia Hughes     Answered On: Aug 18

A coder never fails to miss the unique requirements of the project

We have shown you how to check column B (2).
You are free to modify the code to include other columns A(1), C
(3)... or build a loop to do that.
Alternatively, if that is your intention, join columns A, B, C...
with =A1 & B1 & C1... in a helper column before comparing.

Didn't find what you were looking for? Find more on compare data from different worksheets Or get search suggestion and latest updates.