Comparing Cell Values In Two Worksheets

May 11, 2007

I am aware this question must have been asked to death on this forum and I already searched and found some answers but for some reason I can´t get it to work. I have two worksheets (sheet1 and sheet2). In Column E of Sheet1 I have product descriptions that I want to compare cell by cell to the product descriptions in Column F of Sheet 2. If they are not the same, color in the cell in Sheet 2. There are 1810 rows in both columns. I tried this method but it gives me an error (in spanish) that says that I cannot compare ranges of data.

I have also seen some code to do something similar (http://www.ozgrid.com/forum/showpost...85&postcount=3 ):

Sub find_cell()
Range("A:E"). Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate
ar = ActiveCell.Row
ac = ActiveCell.Column
' the two lines above store the row and column values of the cell found
' in your example ar is going to be equal to 229 and ac is 3
' now this following lines are to change the colors to red
With Cells(ar, ac).Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End Sub

I don´t quite get the "Range("A:E").Find(What:=Cells(2, 6).Value, LookAt:=xlPart).Activate" part though.

View 7 Replies


ADVERTISEMENT

Comparing Cell Values?

May 28, 2014

I have a football score system and wish to compare the values of various cells - 2 if possible, but more if not possible.

Essentially I have a list of predictions for the score of a game (e.g. 2-1, 1-0, 0-0, 2-2 etc) in a spreadsheet, and I have a cell where I enter the correct scoreline (e.g. 2-1). The way the points system works is that if the prediction is correct (i.e. 2-1) then that player gets 3 points. If the result/outcome is correct but the scoreline is incorrect (i.e. 1-0) then the player gets 1 point. If they result/outcome and scoreline are incorrect, then that player gets 0 points.

Ideally I would link to compare 2 cells - predicted score and actual score, where the format of the scores is entered as 2-1.

However, I feel this may be easier having to columns for the score - Column A = 2 Column B = 1. In this way the scores only need to be compared cell by cell, however they would need to be compared by value for the correct outcomes.

View 5 Replies View Related

Comparing Previous Values Of A Cell

Nov 4, 2009

I have a cell (F2) that is auto updated with total sales units for today (Its a simple counter of individual sales). I want to detect when there is a "batch" of sales >= 10. ie. F2 may start at zero for the day and increment by 1,2,5 or 10 units at a time. If the increment is 10 units or more I would like to detect this and store the result in cell G4.

Typical values in C2: 0, 1, 6, 7, 9, 19, 21, 22 etc as new orders arrive. The larger order between 9-19 (viz 10 or more), I need to detect and store in G4. I know that I need to use both the current value of F2 (say 19) and the previous value of F2 (say 9) to perform this, but the solution eludes me.

View 2 Replies View Related

Comparing Cell Values And Moving Whole Rows?

Jul 2, 2014

I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.

In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.

Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE

SUPPLIERSCODE
WAREHOUSE STOCK

And then cut out the rows in which G

View 9 Replies View Related

Automating Comparing Cell Values And Then Processing A Formula

Apr 10, 2007

I have the need within a spreadsheet to automatically compare the value of 2 cells and if they match exactly to move the cursor to a group of cells. If they do not match then move to another group of cells. The only way that I can see to do this is via Macros. However I still need to do the automatic compare first.

View 9 Replies View Related

Comparing Two Worksheets ....

Sep 8, 2007

have been trying to write a VB macro to compare two spreadsheets. Here is what I need help achieving....

Compare Sheet1 and Sheet2. Data exists in rows that have a unique identifier in one column.

If unique ID (with row of data) exists in Sheet1 and NOT Sheet2, add row from Sheet1 to Sheet3 and mark "REMOVE" in new column next to row.

If unique ID (with row of data) exists in Sheet2 and NOT in Sheet1, add row from Sheet2 to Sheet 3 and mark "ADD" in new column next to row.

If unique ID (with row of data) exists in both Sheet1 and Sheet2, take row from Sheet2 and place in Sheet3 and mark "UPDATE" in new column next to row.

View 9 Replies View Related

Comparing Two Worksheets

Feb 15, 2010

Worksheet 1 has 200 rows, Worksheet 2 has 500 rows. Common denomentor in column A. Please advise how I can get 200 rows from w/s 1 pulling the relevant data from both worksheets into a new worksheet

View 9 Replies View Related

Comparing Data Between 2 Worksheets

Sep 19, 2013

I currently have 2 worksheets worth of data in the same workbook. Each data set has its own date and time stamp in columns A and B respectively with varying data then following in the row. I would like to write a macro that would look in worksheet 2 and find the matching date and time stamped row in worksheet 3. Then select the data from both worksheet 2 and worksheet 3 and paste them in the same row in worksheet 1.

View 9 Replies View Related

Comparing Data Between Worksheets

Feb 15, 2013

I have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries.

However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.

How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?

View 2 Replies View Related

Comparing The Same Cells In Two Worksheets

Aug 22, 2008

I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.

More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.

I know that there has been similar requests on this forum (I searched and read them) but thew were different.

View 14 Replies View Related

Comparing Data In 2 Worksheets?

May 7, 2012

I would like to compare the data between 2 worksheets ("Sheet1") and ("Test") so that the contents of any cells in "Sheet1" that are different to the corresponding cells in "Test" are highlighted with a yellow background.

View 5 Replies View Related

Comparing Data Between Worksheets

Feb 15, 2013

I have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries. Fine.

However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.

How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?

View 1 Replies View Related

Conditional Formatting - Comparing Worksheets

Dec 5, 2008

I have 2 worksheets - one is a monthly update, the other is last month's data.

I want to highlight the changes on the monthly update sheet using conditional formatting.

I named the columns as ranges on the "last month" worksheet, and then used =Match(A1,Jan,0) (wheras Jan is the range on the "last month" worksheet where A1 resides)

What formula do you use to say "if A1 does NOT match a value in range "jan", then format it"?

View 9 Replies View Related

Comparing Worksheets Within File / Files

Apr 6, 2011

I have a user that needs to compare MSExcel files with a mass number of worksheets within the file/files. Is there anything to compare Excel files and to print out the compared results?

View 3 Replies View Related

Comparing Worksheets - With Some Items Deleted

Jun 13, 2006

I have a task that I thought would be quite simple but I can’t find a solution in any of the books I have or on this forum.

I receive a list of companies each day who owe money, in Worksheet1. The next day I receive an updated list in Worksheet2. Those companies who have now paid have been deleted from the list. I want to compare the two lists and highlight those companies in Worksheet1 who are no longer in Worksheet 2.

I’ve tried to do this by getting the values in the list in Worksheet2 to loop through the list in Worksheet1 but can’t make it happen.

I’d be really grateful if someone was able to help me with the code I need.

View 4 Replies View Related

Comparing Two Worksheets And Copying Results To Another

Feb 27, 2007

In my attached example you can see I have Sheet "Computer" & Sheet "RHN" both have a number of columns, but all I want is to compare Column A of "Computer" with Column A of "RHN" and copy all entires found in A "Computer" and not found in A "RHN" to a new sheet!

View 4 Replies View Related

Remove Duplicate Information After Comparing 2 Worksheets?

Jul 3, 2012

I have a file with 2 worksheets:

The "On" worksheet represents ALL of the students that LIVE on campus (freshman, sop****re, juniors, seniors)

The "JS" worksheet represents all the juniors and seniors going to school here

I need to know which of the JS live OFF campus.

So.....I need a macro that will compare the JS worksheet to the On worksheet and create a new worksheet called Off and populate it with those JS that are not in the On worksheet.

The "On" worksheet contains:

A B
Last First

The "JS" worksheet contains:

A B C D E F G
Last First Street Street2 City State NY

The new "Off" worksheet should contain the same columns as "JS"

View 3 Replies View Related

Macro To Give Count Of Unique Values After Comparing Comma Separated Values?

Mar 2, 2014

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

View 3 Replies View Related

Comparing Two Columns On Different Worksheets And Adding Rows Where Matches Occur

Jan 18, 2014

Essentially what I want to automate is a check through one list on the Sheets("Notes") in column A, with another column B on Sheets(template). If there is a match I want to insert a part of the row that the match occurred on Sheet("Notes"), and insert it above the row where the match occurred on Sheets(template).

Here is my code so far, currently I keep getting a "Application-defined or object defined error" on the line

VB:
Sheets("Notes").Range(Cells(i, 2), Cells(i, 11)).Copy
VB:
Sub add_notes(template As String)
Dim Rng As Range
Dim i As Integer

[Code].....

View 5 Replies View Related

Comparing Entire Rows Within 2 Worksheets And Then Display Output In Another Worksheet Using VBA

Feb 6, 2014

I'm trying to compare 2 worksheets with the same headers(NAME, ADDRESS, CONTACT NO...). What codes should I use that when there is a duplicated row in worksheet A and worksheet B, it will be deleted and when there are 2 names with different addresses and/or contact no, the whole row in file A will be obtained. Output should also be in another worksheet. It goes like this.

worksheet A
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456

[Code]...

So the output should be:

worksheet C
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456
JIM JKL 345
SAM FGH 789

Is this possible in vba?

View 1 Replies View Related

Excel Macro For Comparing 2 XLS Files With N Number Of Worksheets And Columns

Feb 24, 2014

Need a excel macro which can compare 2 xls files and highlight cells which are different in the 2 files its like a slightly advanced one >a prompt which asks file 1 after selecting file a prompt whichasks for file 2 ..then on running it should highlight cells in file two which are different from file one Both file have same layout ie if file one has n columns in n worksheets file 2 will also have same . So the comparison should be done for each worksheet in both file and cells should be highlighted ..

View 1 Replies View Related

Delete Row Of Duplicate Column Cells By Comparing 2 Columns Across 2 Worksheets

Mar 14, 2008

I am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.

View 3 Replies View Related

Comparing Input Value From Set Of Values 1 And Output Data From Set Of Values 2

Sep 28, 2012

Got a road block on one of the trackers which I am trying to create which compares some of our agreements. Basically we need to compare current production with our contracts and then bring the tier level they are at in order to then calculate how much in overrides we receive. Belwo is an example:

Input Value = 140000
Out Should be = 1.5%

Set of Values 1
T1
T2
T3
T4

[Code]...

In a nut shell it will look for 14000 in set of values 1 and pick T3 to then go in set of values 2 and display corresponding T3 which will be 1.5% in above example as output.

View 2 Replies View Related

Comparing Values (to See Any Duplicated Values Using Macro) ?

Sep 14, 2008

on a sheet i have 2 column with values.

For e.g

DevID DevNum
123 s123
123 s234
987 sabc
987 sabc

Is there a way where i can compare the values in DevNum with the same DevID
to see if theres any duplicated values using macro?

View 10 Replies View Related

Take Cell Values From Six Worksheets And Adds Them Up As A Percentage

Jan 27, 2010

How would I write a formula that takes cell values from six worksheets and adds them up as a percentage (quarterly updates)? I realize this is wrong, but here's what I'd tried (for three sheets).

=+SUM(IF(EVH!F7="Y",25,0)+IF(FUN!F7="Y",25,0)+IF(HES!F7="Y",25,0))/3

View 9 Replies View Related

Comparing 2 Values.

Nov 5, 2008

Comparing 2 values.

I need to compare the value's on 2 cells. I have a sheet setup for my sales I would like to compare each day of the week. So in other words I would like to compare this Monday with last Monday and know what the percentage of my sales ether up or down is.

Example:
Last Monday = sales $100.00
This Monday = Sales $200.00

100%

So If sales are up I would like the percentage amount font to be in green. But if sales are down I would like the percentage amount to be in red.

View 2 Replies View Related

Hide Or Unhide Worksheets From Cell Values In Sheet 1

Feb 18, 2009

I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.

I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.

View 7 Replies View Related

Combining Text And Cell Values From Multiple Worksheets

Dec 1, 2013

I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.

Sub Header()
Worksheets("Header").Activate
ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' "
End Sub

how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?

View 2 Replies View Related

Extract Cell Values By Conditions From Multiple Worksheets

Jun 25, 2008

I am trying to write a Macro that would go into same specific cells in multiple worksheets and paste the data into one summary worksheet if ofcourse a criteria is met.

The multiple worksheets have the same format. The worksheets are numbered 001, 002, 003, 004, etc (increase on a daily base) ..

View 3 Replies View Related

Comparing Values In Two Columns?

Jul 13, 2013

There are two columns A and B and values there in

A B
1 1
2 2
3 3
4 4
9 5
10 6
11 13
15 15

As you can see there are certain numbers that are missing between column B and A. I want to know which of the numbers that are present in column B are missing in column A ...any handy calculation ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved