Compare Rows On 2 Sheets And Replace

Mar 2, 2007

I'm trying to do is compare 4 columns on 2 sheets. I would like to go down in column "E" on sheet1 and find the exact one on column "A" on sheet2. Once these are equal I would compare F, G, H, on sheet1 with B, C, D on sheet2. If the these three columns are not the same I would like to copy from sheet2 B, C, D from sheet2 to F, G, H.

The problem I have is none of the rows of information will be in sequential order as well as we may not have a match. What I'm saying is row1 on sheet1 could be row1000 on sheet2. Attached is a small example. in the example rows 6, 11, 12, 16 and 19 would be the only changes on sheet1. My example does have the rows in sequential but that would almost never be the case for me....

View 9 Replies



Jun 30, 2007

at first i wrote company's name short so that i have to type less but now i want full name of company.....

like.....if in sheet#1 column B any company's name is started by Rel of sheet#2 column A , then the cell containing Rel is replaced by Reliance Industries ( sheet#2 column B but same row that of Rel) sheet#1


Rel...............................Reliance Industries

View 9 Replies View Related

Compare Rows Between Sheets

Aug 22, 2006

I have 2 sheets that have 15 columns and approximately 200 rows on average, but the number of rows varies from week to week. I need to compare the information on sheet 3 to the information on sheet 1 by row. There are actually 3 worksheets total, but the 2nd worksheet is not involded in this macro. If the 2nd-14th columns are an exact match, I need to have the date from the first column and the notes from the 15th column (the formatting has to be copied too!!!) on the first sheet copied over to the matching row on the third sheet into the 1st and 15th columns, and the 16th-18th rows on the first sheet need to be copied over to the third sheet as well. After all this is done, the row on the first sheet needs to be deleted.

I need it to compare all of the rows this way, and unfortunately, I think it is going to have to loop through every row on the 1st sheet for every row it is comparing from the 3rd sheet.

I know this will take quite a while to compare, but given this takes me a couple hours on average, letting the macro run for 5 or 10 minutes is a vast improvement

View 9 Replies View Related

Count & Compare Used Rows On 2 Sheets

Aug 8, 2008

I want to count the number of data rows in Sheet 1, Count the number of data rows in Sheet 2 and display a message if they are not equal. I have tried various IF(COUNTA statements but cannot get the correct syntax.

View 2 Replies View Related

Compare Two Sheets And Remove Duplicate Rows To New Sheet

Jul 30, 2014

The following code compare two sheets and remove the duplicate rows to a new sheet. In this code I have to create the third sheet manually.

I need the "New Sheet" to be create automatically with the name of "Dup".


Option Explicit
Sub nomatchnoty32()
Dim X, i As Long, j As Long, Y(), Dic As Object, k&, Z, name1$, name2$

Const shSource1 As String = "sheet1"
Const shSource2 As String = "Sheet2"

[Code] .........

View 6 Replies View Related

Compare Cells Across 2 Sheets & Insert Rows If Criteria Met

Apr 26, 2008

I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.

(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)

To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.

I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".

View 4 Replies View Related

Compare And Replace ...

Aug 25, 2009

I have 2 Excel files that have 2 columns in each.

Product# and Price.

The Product#'s in the 1st sheet are in correct order and have outdated prices.

In the 2nd file the Product#'s are same but they cannot be sorted the same way as the 1st file. So I cannot just copy the column of updated prices from 2st one and paste it into the 1st.

So, my best bet would be to run a script or something that will get a Product# from 2nd file and find it in 1st, if it matches then copy the new price to the 1st file.

View 7 Replies View Related

Compare And Replace ..

Apr 26, 2007

I've searched the board for something close to reverse-engineer but no luck. I'm looking for a macro that will:

Examine values in Sheet 1, column B
Compare them to values in Sheet 2, column A then...
wherever there's a match copy the the corresponding cell contents of Sheet 2, column B into the corresponding cell of Sheet 1, column A

What I'm trying to do is append the description of a 4-letter code (from a reference Sheet 2) beside where that code appears in Sheet 1.

There won't be a row-to-row correspondence in the match, i.e ABCD might be in (Sheet 1)B45 and (Sheet 2)A920...

View 9 Replies View Related

Compare And Replace

Sep 7, 2007

My problem description is as follows

I have to create a merged file from two input files.These are

1.The first input file is generated by the tool.This file contains the error message description stored in columns.For the coresponding error message a unique identifier is assigned by tool.

Identifier is in different column in same sheet. The error message has title also.

The sheet can be represented as follows ....

View 9 Replies View Related

Compare Columns And Replace Values Without Overwriting

Mar 14, 2013

I have 2 sheets similar to below :

apple pc
sony camera
apple laptop

sony television

I need to compare Column A from Sheet2 with Sheet1 and where the values match, only replace THOSE values in Column B of Sheet1 with those in Column B of Sheet2. Hence, after the replacement, Sheet1 should look like (value for apple remains unchanged).

apple pc
sony television
apple laptop

What would be a formula that would do it ?

View 1 Replies View Related

VBA Solution To Compare/replace Data Between Columns

Jul 4, 2009

I have two columns of adjacent data Call them A & B (although this could differ)

If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.

View 9 Replies View Related

Macro - Replace Words In Sheets?

Oct 31, 2012

Trying to do:

Where ever "New Orleans Saints" shows up in my sheet. I want to replace it with "NO"

Where ever "Washington Redskins" shows up in my sheet. I want to replace it with "WAS"
This continues on for 32 different teams.

View 4 Replies View Related

Find & Replace Data Between 2 Sheets

Jan 30, 2008

I have in sheet1:

Item QTY
A 1
B 10
C 0

In sheet2 I have

Item QTY

View 6 Replies View Related

Compare The Sheets..

Oct 17, 2009

I know,there are "compare sheets" threads everywhere,but i think every thread is different,because we want different comparison and different results

To the point now.What i actually want to do is a "turnout" of difference between two sheets.Both sheets use columns A and B. On column A there is a code and column B the quantity of the code. So obvisously i want to compare those two columns of these two sheets,and paste the results on a different sheet. The difficult part is that the same code of a cell in Column A with its column B must be sorted so i can see the difference...else it would be a mess.

View 5 Replies View Related

How To Compare Two Sheets

Jan 24, 2012

I put the following together. The is that it loops through row A until it gets to the last cell. For each loop it loops down the column to the last row of data looking at the same cell on sheet 2.

But I'm having trouble with the Range(RngCell, Y).select portion. As the active cell can't be a static column letter I need to reference the active column.

Sub SheetComparison()
Dim rngCell As Range, rngMyRange As Range
Dim lngLastRow As Long


View 2 Replies View Related

Compare Two Sheets With VBA

Nov 20, 2004

We have a software that we use for budgeting, and every now and then, we have to export and validate the data in Excel to be in sync with the system. What I would like to do is after extracting the data into a spreadsheet, compare it with VBA and create a report in a seperate sheet, that shows the differences between sheet1 and sheet2. The data has 11 different dimensions from column A to K and is in the same order both in the master and recon files.

Accounting Unit
Lawson Division
Product Line
Mid/View Consolidator
Mgr Responsible
Cost Center
Opex Rpt
Allocation Pool

I have found a compare macro which only compares and finds the exact match of the sheets in exact cells
Sheet1(A1) 1 Sheet2(A1) 1
Sheet1(A2) 2 Sheet2(A2) 2
Sheet1(A3) 3 Sheet2(A3) 3
Sheet1(A4) 4 Sheet2(A4) 5
Sheet1(A5) 5 Sheet2(A5) 6

If this was the case then everything as of cell A4 would be marked as an error, even though I have the value in a different cell in the next sheet.

View 9 Replies View Related

Compare Two Sheets

Oct 17, 2007

I have two spreadsheets each with "Vendor names" on column A. sheet 1 is my master sheet and sheet 2 is the sheet that has the information i like to carry over to sheet 1 when the conditions are true. Hence when colum A on sheet 1 equals column A on sheet 2 copy the range of cells b-h in sheet2 to match to sheet1.

here is my code i figuere that if i stared with two columns it would be rather simple however i cant even get my own code to work. Any help or suggestions would be greatly appreciated. By the way my spreadsheet is 6,000+ lines long

Here is the code

Sub compares()
Dim rng1 As Range
Dim rng2 As Range
Dim RowNo As Long

Set rng1 = Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
Set rng2 = Worksheets("Sheet2").Range("A1", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
For Each c In rng1
If Application.WorksheetFunction. CountIf(rng2, c) > 0 Then
RowNo = Application.WorksheetFunction.Match(c, rng2)
c.Offset(, 1).Resize(1, 2).Value = Worksheets("Sheet2").Range("B" & RowNo, "C" & RowNo).Value
End If
Next c
End Sub

View 9 Replies View Related

Find & Replace On Protected Sheets Fails

Jan 10, 2007

I am attempting to run a macro that will do a find and replace on a protected sheet but this is not working. I am using the following code to Protect all Sheets, which I found on this site when workbook opens using the Workbook Open Event and uses UserInterFaceOnly :

Private Sub Workbook_Open()
Dim wSheet As Worksheet

For Each wSheet In Worksheets
wSheet.Protect Password:="111", _
Next wSheet
End Sub

And then a find and replace similar to this:

Sub FindAndReplace()
Selection.Replace What:="w", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
End Sub

View 6 Replies View Related

Multiple Find And Replace Over An Array Of Sheets

Jul 6, 2007

I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.

Sub Find_And_Replace()

Sheets( Array("Resolution", "Response", "Open")).Select

Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Sheets(Array("Resolution", "Response", "Open")).Select

Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

End Sub

View 8 Replies View Related

Compare Two Excel Sheets?

Dec 18, 2013

i have a list of information on one spreadsheet, for instance in column A i have countrys in column b i have descriptions and in colum d i have names. there is several hundred lines of this information and someone has gone in and edited some, taken a few lines out etc, i was just wanting to know how i can compare the two sheets to see what changes have been made? Or what now doesn't match up etc?

View 6 Replies View Related

Compare And Add From All Previous Sheets?

Mar 4, 2013

Sheet summary2 has the table. And I am looking to compare and add the data (from all sheets Date 1 to Date31) . I am looking to add those values (yes as 1) in my summary 2 sheet for the range E3 to H14 ...if it mathces as MF8330 or x543 or c5045 or x940.

View 4 Replies View Related

Compare Between 2 Tables In 2 Different Sheets

Jun 26, 2014

Macro that compare between 2 tables in 2 different sheets - BOM (Bill Of Materials) compare

And Create a Table with all differences.

Table 1 (sheet1):

000 2 A1
111 3 B1
222 1 C1
333 4 D1

Table 2 (sheet2):

333 3 D1
000 2 A1
111 3 B2
222 1 C1
444 5 E6

Table with all differences (sheet 3):

PartNum (Ver1)PartNum (ver2)QTY (Ver1)QTY (Ver2)Location (Ver1)Location (Ver2)
111 111 3 3 B1 B2
333 333 3 4 D1 D1
Blank 444 Blank 5Blank E6

View 2 Replies View Related

Compare Columns Between Two Sheets

Apr 30, 2009

The code below compares values on sheet 4 column A to sheet 3 column A and then colors a cell "Green" (Temporary), later I will place data from sheet 4 into sheet 3.

I dont know why but when it finds data on sheet 4 that is not 100% numeric it errors out.

Run-time error '91'
Object variable or With block variable not set.

The data in sheet 4 column A is primarily numeric, there are and always will be some numeric/alpha strings.
I can change the value of sheet 4 A2 to "123x" from "123" and the code stops as described. Leaving sheet 4 A1 as 100% numeric, which works fine.

View 4 Replies View Related

Compare Products On 2 Sheets

Mar 13, 2009

I'm not 100% sure how to describe what I need to do but here goes. I have two similar spread sheets. The first contains all the products in a shop with the new prices and new item codes.

The second sheet has all the old item codes old prices and some duplicates and items that do not exist any more. Both spread sheets have lots of column but only 3 that I need to worry about. Item code, Product Name and Price.

What I have tried is using the If command (in the item code column) to search through the Product Names (on the updated sheet) and put the right item code next to the right product (on the old sheet) but it didn't work.

View 5 Replies View Related

Compare 2 Cells From 2 Sheets

Mar 29, 2013

I will like a macro to compare row one to row one on sheet 2 column A. If the number is the same then it will compare from the same row column K from sheet 1 and column m from sheet2. If it is less then copy to sheet4 and highlight in red, if more than highlight in blue and if equal green.

View 6 Replies View Related

Compare Values From Two Different Sheets

Sep 28, 2007

If the record exists in both sheets then compare the value in col. H for both records, if the value is different then copy the entire record to a third sheet.

There are 16,000 rows of data to do comparisons.
I have been thinking about a Vlookup method as one choice, my thought is this going to be slow.
I have read about vba having some comparison choices, vbbinary, vbtext, but I do not have any knowledge of this, so cannot intelligently choose what options to begin w/.

View 9 Replies View Related

Compare Columns From 2 Sheets

Sep 26, 2006

I'm trying to cmpare two excel spreadsheets, each one of then have 3 columns with column A being the KEY to lookup and compare records into another spreadsheet. So in my example spreadsheet If u take cell ZVNA!A1 and lookup for values in ZSKU!A* and find a match then comapre the B1 and C1 in respective ZSKU! Column B and C. If there is a record for A1 in ZVNA then comapre it's column B value with column B value of ZSKU, and column C value with column C value of ZSKU. I have not used macro/vba before so if you could tell me formula based solution that will be good or point me how to use macro. I did Vlookup but it did not return me 100% accurate result may be bec my format of column is not accurate.

View 2 Replies View Related

Compare Values In Different Sheets

Nov 28, 2006

I am in the process of comparing values between two sheets.

I would like to search on column A on both sheets and check whether there is a corresponding value in the sheets.

If there isnt then I would like to take that record and put it into a new sheet.

My code is as follows

Public rowcn
Sub auto_open()

Dim iCounter As Integer 'counter as integer
data_sheet = "CASES_ANALYS"
data_sheet2 = "A_CASES_"
target_sheet = "Final Report"

Currently I'm just displaying it in a msgbox but would like to paste the specific record into another sheet but not sure how to do this.

So far this code doesnt work

View 9 Replies View Related

Compare Two Sheets For Difference

Sep 7, 2007

I have 2 sheets that either contain the exactly or close to same data. I have Sheet1 and Sheet2, and I want to highlight ALL cells in Sheet2 that are not the same as the ones in Sheet1. That is, I need to give a cell in Sheet2 a different background color (to highlight) if:

the cell in Sheet2 has a different value than the cell of the same location in Sheet1
the cell in Sheet2 is blank but the cell of the same location in Sheet1 has something in it
the cell in Sheet2 has something but the cell of the same location in Sheet1 has none.

All highlighting needs to be done in Sheet2. The problem with the codes that are already available in this forum seem to have problems when the ranges of cells used in the two sheets are different (out of bounds, etc...)

View 2 Replies View Related

Find And Replace Matching Cells Across Multiple Sheets

Dec 11, 2013

Following Excel task I am trying to complete:

I have an Excel file with multiple sheets and I want to find and replace matching cell data on the same row across all of the sheets. For example, I have two columns, Column A and Column C and 10 sheets. I want to only replace the content in Column A if text matches both Column A and Column C on the same row. So, I want to be able to search for the following data across all sheets:

Column A = "car"
Column C = "yellow"

If both "car" and "yellow" are found in Column A and Column C on the same row, then replace "car" in Column A with "truck".

Is there a way to do this automatically as I have few hundred to find and replace?

View 5 Replies View Related

Copyrights 2005-15, All rights reserved