Compare Two Spreadsheets Altering 2nd One Based On Contents Of The First
Jul 6, 2007
I have a master spreadsheet containing 4 relevant fields, 'Manufacturer', 'Product', 'Version' and 'Type'. This contains all possible variations of 'manufacturer', 'product' and 'version' that can occur in the organisation.
The type field shows how the particular item is going to be processed, either 'automated', 'manual' or 'non'
This is a computer generated spreadsheet, apart from the 'Type' field which we've entered for each one individually, as are the user spreadsheets which dont yet have the 'type' field.
This is a large list of around 7000 items
I then have a multiple spreadsheets (one for each user, totaling around 1200) that has a list of a couple of hundred items that are relevant to the user which contains 'manufacturer', 'product' and 'version'.
i need to sort these out quickly by comparing the sheet to the master spreadsheet so where 'manufacturer', 'product' and 'version' match up with the masters record it automatically removes the the 'non' type of item from the user sheet and placing either 'automated' or 'manual' in a new 'type' filed where appropriate
View 3 Replies
ADVERTISEMENT
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx‎
View 10 Replies
View Related
Jul 23, 2011
I'm doing an audit where I need to compare data between two spreadsheets that are housed on the same worksheet. The data I'm trying to compare is the ssn#. I'm expecting duplicates, I'm trying to find ssn#'s that do not match up with another. In other words, if one spreadsheet has 92ssn#s, the other has 79, I want to be able to identify those that do not have a matching ssn# from one spreadsheet to the other.
Both spreadsheets have the following columns:
Column A Column B Column C
SSN Last Name First Name
The tab on the bottom of sheet 1 is titled "CX Data", the tab on the bottom of sheet two is titled, "BCBS of AL Data"
in creating a VLOOKUP formula that will meet my needs?
View 7 Replies
View Related
Jan 12, 2012
I have two spreadsheets, and wish to compare some of the objects. I have my vba code set up in Sheet1, and my functions in Module1 in one spreadsheet. I successfully open up the second one, then get this error message:
Microsoft Visual Basic for Applications
Run-time error '1004':
Application-defined or object-defined error.
If I comment out the SwitchExcel line, the script works.
Here is my code:
Dim theRow As Integer
Dim theCol As Integer
Dim strVal As String
Dim LastCellRow As Long
Dim LastCellColumn As Long
Dim YesNo As Boolean
[Code] ..........
Module1 code consists of:
Code:
Function LastCellColumnNumber() As Long
''''''''''''''''''''''''''''''''''''''
' Last cell in column
''''''''''''''''''''''''''''''''''''''
Dim RowNumber As Long
Set WS = Worksheets("Sheet1")
With WS
RowNumber = 2
If .Cells(RowNumber, .Columns.Count) vbNullString Then
Set LastCell = .Cells(RowNumber, .Columns.Count)
[Code] .......
View 6 Replies
View Related
Mar 2, 2010
I have a spread sheet where I need to update columns "H" and "I", the information comes from the host system as an excel download.
Both spreadsheets have the entity id in column "A".
Both spreadsheets are in the same workbook
The tabs are:
CAM Exp
OPEX
What would be nice is a macro that would compare column "A" in both spreadsheets and where a match is found copy the information on the OPEX tab in column "H" and column "I" to the CAM Exp tab in the same columns. (Column "H" and "I")
View 9 Replies
View Related
May 30, 2014
I have 2 spread sheets
One is just email addresses
a@a.com
b@b.com
c@c.com etc.
The other contains data and also these email addresses I need to compare the 2 and when a match is found change the email address in the data table to something else, say "tony".
View 6 Replies
View Related
Mar 12, 2009
I am a relatively light Excel user. I mainly use it when working with the .dbf files that make-up GIS shapefiles.
My problem at hand: How can I compare/isolate the rows from spreadsheet #1 to #2 to determine which rows are unique to spreadsheet #1?
What I'm doing: I have a spreadsheet of addresses which I joined to our parcels shapefile to select those parcels. I am trying to determine the success rate of my join operation by isolating the rows which weren't joined. This would be determined by highlighting which rows from spreadsheet #1 aren't in #2.
View 4 Replies
View Related
Mar 27, 2009
I have two spreadsheets SP1 and SP2. I need to match values in column E of SP1 with values in column A of SP2. If I find a match in SP2, I need to find a value starting with 'TC_' from the previous rows and get the corresponding value in column B of SP2 and paste in column F of SP1.
For example, E1 value in SP1 matches with A3 value in SP2. So I need to find the row with value TC_AM01_axs_fhgn and get the corresponding value in column B AM01 and copy it in column F of SP1.
The two example spreadsheets are given below. The values in SP1 sheet has expected result in column F in red.
SP1
SeriesS.NoItem #CaseReuse CompScenario #A1abc001_CTC001AM01B2bcd002_ATC002AM02C3def003_BTC003AM03D4efg004_DTC004AM04E5ghi005_ETC005F6hij006_FTC006G7lmn007_GTC007H8opq008_HTC008I9rst009_ITC009J10uvw010_JTC010K11xyz011_KTC011
SP2
Case #SubjectTC_AM01_axs_fhgnAM01TC001asdhagsdjharteuTC_AM02_axs_fhgnAM02TC002asdhagsdjharteu12sdfsiu786dfgg88hdcfs676566532kjjjiTC_AM03_axs_fhgnAM03TC003asdhagsdjharteuTC_AM04_axs_fhgnAM04TC004asdhagsdjharteu
View 10 Replies
View Related
Mar 17, 2007
I have two spreadsheets that I have to compare three columns in each to each other and find the difference between them. And I'm not sure how to do this.
I have attached a sample file to show what I'm looking at.
Basicly I need to know the differences between each spreadsheet based on zips. Each zip is assigned to a store and group and the "data" spreadsheet is the master. I need to compare the "system" spreadsheet and have it show me what is different based on each zip.
View 9 Replies
View Related
May 4, 2007
I am trying to compare values in column "A" of my PRIMARY spreadsheet with values in column "A" of my SECONDARY spreadsheet. If match in PRIMARY is found in SECONDARY, copy column b and c from SENCONDARY and paste in b and c of PRIMARY. Next, test for next true statement until no other matches in PRIMARY.
Any ideas of how to quickly and efficiently accomplish this task? Actually, I am now copying a spreadsheet from another workbook and pasting the content to another sheet which I am referring to as SECONDARY in the previous paragraph.
View 9 Replies
View Related
Feb 5, 2009
I have several old spreadsheets that contain patient information. Except for names, dates of birth, and dates of surgery there is nothing consistent about them. Not only that, the old spreadsheets were in various stages of compiliation and recompilation when they were abandoned. So now there are multiple duplications of old names added to lists of new names spread across multiple spreadsheets. I have to identify names that are not already in my database and add them.
I have a formula that I found on the internet that I can use to check if a patient’s last name in an old spreadsheet is one that already appears in my main database. In my attached example the formula looks like this:
View 3 Replies
View Related
Nov 17, 2011
How to compare two spreadsheets in the same workbook in Excel 2010.
View 4 Replies
View Related
Feb 22, 2007
I have two worksheets with products that I need to compare.
Each product has a code and a product name in the following format:
Column A has the product code and column D the product name,for
example:
ColumnA:
AM1BL15X
ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244
Both sheets *should* have the same data in them but there are 4000
products that need to have their description verified.So I need to go
down each row on Sheet1 , extract the product code from
Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a
search on Sheet2 for the product code (Sheet2.ColumnA) and verify that
the product description on sheet2 (Sheet2.ColumnD) is the same as in
Sheet1.ColumnD. If it matches,everything is OK. If not there is
something wrong. So I´m thinking that maybe the product codes that
dont have matching descriptions could be entered in to a separate
worksheet so that someone can check it later.
I´ve been reading around and found these code samples from this site
[url]:
Dim rng1 as Range, i as Long
Dim cell as Range
With worksheet("Sheet1")
set rng1 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
i = 0
for each cell in rng1
if cell.Value worksheets("Sheet2") .Range("A1").Offset(i,0).Value
Then
' do what - they don't match
else
' do what - they match
End if
i = i + 1
Next
I´ve been trying to make some changes but I dont quite know how to get this working to do what I want and I only get errors.
View 9 Replies
View Related
Feb 22, 2007
I have two worksheets with products that I need to compare. Each product has a code and a product name in the following format: Column A has the product code and column D the product name,for
example:
ColumnA :
AM1BL15X
ColumnD:
AGLO MEL BLANCO 1C 15MM 215X244
Both sheets *should* have the same data in them but there are 4000 products that need to have their description verified.So I need to go down each row on Sheet1 , extract the product code from Sheet1.ColumnA and the product name from Sheet1.ColumnD. Then do a search on Sheet2 for the product code (Sheet2.ColumnA) and verify that the product description on sheet2 (Sheet2.ColumnD) is the same as in Sheet1.ColumnD. If it matches,everything is OK. If not there is something wrong. So I´m thinking that maybe the product codes that dont have matching descriptions could be entered in to a separate worksheet so that someone can check it later.
I´ve been reading around and found these code samples from this site ...
View 2 Replies
View Related
Jun 9, 2014
I have two sheets:
- sheet 1 ('current')
- sheet 2 ('31May')
The data in column C is the same, although sorted differently and can't be re-sorted to be the same. Where the data in column C is the same in both sheets, I want to fill columns A and B into sheet 1.
View 4 Replies
View Related
Sep 30, 2011
I'm a long time excel user and I've come up with an issue, it can surely be solved in any other larger database language but excel is just my everyday friend and I was wondering if it's possible to do such a thing.
I have a list of "people" in my 1st ssheet and they have
ID | ProductName| Category | Year |
and I have this data on the 2nd ssheet
ID | Price | Description
There is 600.000 records in the primary sheet but only 22.000 in the secondary because the data doesn't repeat (one description can fit to 10.000 products, no need to write it down that much times).
But now I need to compile one big/large file, so I need to have one file that contains all:
ID | ProductName| Category | Year | + | Price | Description
In MS SQL I would use the "Where" function and compare the ID here and there, but is there a possible way to do it in excel?
View 6 Replies
View Related
May 13, 2006
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet
2. repreat until all rows in worksheet 1 are read
3. save worksheet 3
View 3 Replies
View Related
Dec 18, 2013
I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.
View 14 Replies
View Related
May 21, 2013
I have a very large workbook, too big to cut down and upload . In it I have some code which selects a graph and sets the data on the graph to an arbitary number, Unfortunately, I get a "Method 'Xvalues' of object 'Series' failed" error when I run it (although not every time). The relevant part of the code is below. Prior to the code below, different sheets are selected and variables moved around, then this part of the code hides the other sheets leaving the calculation page visible.
The graph is on a log-log scale, but I don't think that is particularly important. The data series are named as various things (e.g. baseline, reference, measurement) though but there are only ever two on the graph (I just rename them later on and change the data).
VB:
'open calculation page and hide survey results and archive pages
With Worksheets("Calculation Page")
.Visible = True: .Select
[Code]....
View 9 Replies
View Related
Mar 31, 2014
I need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
View 10 Replies
View Related
Mar 26, 2013
I have come across another stumbling block with VBA (new on VBA and slowly taking baby steps). I have a macro that reads and deletes all duplicates on the files i work on. However I need this to read and do the job on 2 sheets at the sametime (sheet 2 and sheet 3). Below is the macro which does work fine on a different workbook. How would I integrate this into my new sheet to do 2 jobs at once?
VB:
Sub delduprows()
Dim m As Integer, j As Integer
Dim n As Long, i As Long
[Code]....
View 1 Replies
View Related
Jun 12, 2014
I have a spinbutton on a userform. Here is the section of code for SpinUp:
[Code]....
Normally, I wouldn't hard code an address in like this, but I was given a last minute project to present on Monday, so it's down,dirty, and quick. Anyways, the adjacent cell "O7" has a formula referencing another page. When I spin the spinner it activates the correct cell (and continues to do so as long as I press the button), but here's the wierdness: cell "07" formula disappears, leaving the existing value in it. It's like it copy pastespecial values.
I've stepped through the code and it's not referencing any other procedure. It happens on this line:
[Code] ....
View 1 Replies
View Related
Sep 4, 2007
In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
View 9 Replies
View Related
Jul 18, 2006
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
View 3 Replies
View Related
Apr 3, 2008
i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
View 12 Replies
View Related
Dec 28, 2012
I have a column ful with numbers and would like to add an h (hour) after every number. I found that it is possible for valuta (dollar sign). Is there a way for random symbols that are not in the valuta list?
View 3 Replies
View Related
Nov 12, 2008
I have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.
Ive written some steps explaining what each table does here -
Step 1Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2Check corresponding column destination available capacity in table 2 (Example 500)
Step 3Distribute as much as possible from source in table 2 (500)
Step 4Reduce value in capacity line by value taken from source
Step 5If some source remains move back to table 1 and find next nearest column Dest
Step 6Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
etc
until Table 1 column B is empty
Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.
View 14 Replies
View Related
Apr 17, 2007
I have a workbook with four worksheets. Sheet1 is the master. On sheet one there is information in a 10x10 table. The first column of the table contains a status of "No, Maybe, Yes." A record will start with a status of No and move to Maybe and then yes. What I want to do is be able to dynamically fill sheet2-sheet4 with information from the master. If the status is "Yes" move that row to sheet2. If the status is "no" move that row to sheet 3. Etc. Further complication that is depending on status, I want specific columns from the master, not necessarily all of them.
View 2 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Mar 31, 2009
--------------------------------------------------------------------------------
Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:
=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)
This allows me to type 101-16 in and excel recognizes this as 101.5
My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).
I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View 11 Replies
View Related
