workbook1 column A has tag numbers, workbook2 column A also has tag numbers. I want to compare the 2 columns and if the tag number in book 1 is also in book 2 i want it to return 25B-F138-28-01 in column B workbook1.
I work for a financial firm. Every morning I compare today's cash balance file (sent from our custodian) with yesterday's to see if there is anything different in each portfolio cash balance column. Right now I'm doing it manually checking row by row. It'll become tedious if more and more portfolios are added. I'm looking for an efficient way I can use the portfolio codes column (as a key) to compare with previous day's file’s portfolio codes column , and list only those portfolios(in another workbook) if the corresponding cash balance column doesn't match. This is what I started doing:
Private Sub compare_cash_bttn_Click()
Dim b1 As Workbook, b2 As Workbook, b3 As Workbook
Dim w1 As Worksheet, w2 As Worksheet, w3 As Worksheet
Dim i As Integer
i = 2
Set b1 = Workbooks("CashA")
Set b2 = Workbooks("CashB")
'Set b3 = ActiveWorkbook
Set w1 = b1.Sheets(qry4rCash).........................
way I can check workbooks, and compare it to make sure they have the same exact data. What I would like to do ---
-I have 76 workbooks, and in each workbooks I have 12 worksheets -I would like to compare, Table01_cue.xls to Table01_Master.xls to make sure that A1:N76 (sheet Jan) in Table01_cue.xls match with A1:N76 (sheet Jan) in Table01_Master.xls, The range in each worksheets changes … for sheets Feb it is range from A1:O76… is there a way to check this?
And if the data doesn’t match up I would like it to tell me where it doesn’t match up…
1) if Column M has the value "School" then corresponding value in column N should have oly "Bus","Subject","Teacher". if values in Column B other than "Bus","Subject","Teacher" then the cell should populate RED Color.
2) if Column M has the value "College" then corresponding value in column N should have oly "Box","Madam","World". if values in Column B other than "Box","Madam","World" then the cell should populate RED Color.
I have a table with many columns of data on sheet1
Is it possible to have sheet2 have ... oh I dont know... say 5 columns. In which I can choose the headers that correspond to the headers in sheet1. Therefore comparing the columns that I want simply by changing the header?
What I'm attempting to do is analyze data on spread sheet. I've attached an example. what I need to do is compare columns "E" and "I". But I have numerous means of comparing the data. I think I will need three different formulas which is okay and am unsure if VBA is better. For example: I compare "E2" and "I2"...ect: 1) I would need to compare complete cell 2) I would need to compare the first three digits 3) I would need to compare the middle digits If you towards the end you can see that some will compare to a BLANK cell if column "E" is ever blank. I first tried using countif but couldn't get it done. I only need totals that do not match in this category.i.e. 25 of 140 do not match.
I have currently browsed the forums and have came up with a code to compare two columns from two separate excel books and then highlight anything matching with the CompareRange. Here is a few more details about the problem:
- I have two excel sheets. And data like this in each sheet:
(First Sheet) (Second Sheet) -A B N O -7 .7 3 .56 -6 .6 8 .45 -5 .5 9 .55 -4 .4 11 .2 -3 .3 8 .22 -2 .2 9 .55 -1 .1 8 .54
As you can see, given this example nothing should be highlighted once the macro is run since nothing from Column A or B from the first sheet matches directly with Column N & O from the second sheet. The problem is that with the macro (module) I have come up with will highlight "3" from Column A and ".2" from Column B, just because they appear in Column N & Column O respectivally. What I want: I only want a number to be highlighted if both the numbers "7" & ".7" are matched in the same row of Column N & Column O on the other spreadsheet. To be a little more precise, I'll give an example. Say I edited the data to be like this.
(First Sheet) (Second Sheet) A B N O 7 .7 3 .56 8 .45 8 .45 5 .5 9 .55 11 .4 11 .2 3 .3 8 .22 2 .2 9 .55 1 .1 8 .54
With this data, I would want the second row of A & B ("8" & ".45") highlighted, while my error "3" of Column A and ".2" of Column B is not highlighted. Also, I would like it if row 4 of Column A & B ("11" & ".4") is not highlighted at all either, just because in O it is .2 and in B it would be .4 even though the 11's match. Attached is the macro/module I have entered in which is working kind of correctly but producing the mistake. And also, (kind of a lesser problem), both the files with data will have the same header, example would be if Column A & Column N both had "Dogs" as it's title in Row 1 and Column B & O both had "Cats" as it's title in Row 1. Is there anyway the macro can be adjusted so it compares those two columns between the two workbooks without me even having to select or assigning a range?
HTML Code: Sub Find_Matches() Dim Column1 As Range Dim Column2 As Range
I have column A which contains a range of part numbers, and column B is an On Hand quantity of that part number.
Column C is just like column A, in that it contains the same part numbers but it has about 1,000 additional part numbers that are obsolete. Column D has the On Hand quantity for Column C.
Column A and B are old information as far as the On Hand quantities, where Column C and D are up to date On Hand. The problem is the old outdated part numbers in column C.
How do I make column A and column C match, but not lose their respective On Hand quantities? I know this sounds discombobulated, but basically I need column A's part numbers, with column D's on hand quantity.
I'm trying to compare addresses in a worksheet, column A and column C have the addresses in them, in column A there are 44063 rows and column C has 43751 rows , both columns should have the same number of rows in each column because they should have the same number of addresses in each column but they dont so what i need to find out is which addresses from column A arent in column C and which addresses in column C arent in Column A and have the result put in column D.
I have need to do a comparison of 2 values located in different columns. As this is almost impossible to describe in text format, I have included a spreadsheet showing exactly the problem. Im sure the solution will involve INDEX and MATCH but every permutation I have tried has failed (possibly theres another way?).
with hello and regards two all members and administrators i have this table COUNTRY1EXPORT$COUNTRY2IMPORT$SAME CODEEXPORT$IMPORT$30% MINOR 1019011710190294 102107410210138 10290161029029 10511271051111 105191271051970 106391271063947 10690191069047 ………… ………… ………… 9617001996170018 9618005589618001 97011097097011027 9701901169701903 97050009705003 970600097060091,969
Column A: Country1 Export Code Column B: Export value for Country 1 Column C: Country2 Import Code Column D: Import value for Country 2 ------------------- NOW --------------------- i want to fill in Column E to H with a macro Column E: same code in Column A and Column C Column F: export value for same code (from Column B) Column G: import value for same code (from Column D) Column H: 30 percent of Minor Value for example if F3 is 9000 and G3 is 1000 then H3 will be 2700 that is 30 percent of F3
I need to compare data across two columns: the date a claim was filed (column T), and the date it was resolved (column AH).
On my summary worksheet, I'm displaying the claim details for each month. I need to show how many of the claims filed (T) in each month are resolved (AH) - and for this output I'm not concerned about when they were resolved. This lets us track if there's still money due to flow out of the coffers for old claims. For example, there were 5 claims filed in July, and 4 of them are resolved. I want my summary tab to return a value of "Completed" beside July when I enter the resolution date for that last one, and "Pending" until then.
All the fields I'm describing are formatted as dates - including the name of the month (eg, "July" is displayed because I've put a custom format of "MMMM" on 7/1/08).
I've been playing around with countif and sumproduct but can't quite figure this one out.
I have data in column A which is the correct data. I also have data in other columns that I would like to compare to column A. If the data is the same, then it moves beside it, if not then it leaves a blank cell.
I have looked for macros and formulas to do this for ages but i cant seem to find one. Here is an example
A B C D 1 1 2 1 2 4 3 2 3 5 4 4 4 7 5 6 5 - - 7 6 - - - 7 - - -
A B C D 1 1 - 1 2 - 2 2 3 - 3 - 4 4 4 4 5 5 5 - 6 - - 6 7 7 - 7
Right now i have a list of data in text format....they are stock tickers. One column is about 2000 entries long and the other is 500 entries long. I need to compare the two columns together and highlight the entries in the long column when they match to an entry in the 500 column. I have tried so many different formulas and conditional formatting on my own and can't figure it out
I want Excel to compare two columns of numbers. Column R has 1000 entries, beginning with the number 1 and ending with the number 1000. Column S will have from approx. two hundred to as many as six or seven hundred entries, looking something like this: 3, 5, 6, 10, 11, 13, 14, 15, 18 . . . . . . 990, 992, 995, 996, 999, 1000. I want Excel to compare column S with column R and display the difference in Column T. Column T will therefore look like: 1,2,4, 7, 8, 9, 12, 16, 17, 19 . . . . . 899, 991, 993, 994, 997, 998. I have to do this multiple times, does Excel have a built-in function(s) that can do this or do I need to write a macro? The numbers can be formatted as text if neces- sary
I'm trying to determine if one date meets the criteria to be considered 'on time'. I have two columns: estimated completion date and completion date. I want to compare the completion date to the estimated completion date and if completion date is <= completion date but is NOT = 1/1/2099 then I met my date. I've tried this several different ways and problem is I cannot make it work.
When estimated completion date is 1/1/2099 'on time' should be 'no' When estimated completion date is not 1/1/2099 but is greater than completion date 'on time' should be 'no' When completion date is <= estimated completion date and estimated completion date is not equal to 1/1/2099 'on time' should be 'yes'
And I have a null date to deal with also but if I can satisfy the first 3 criteria I can manipulate the null values manually.
I have two columns of card numbers (16 digits) in text format, as column A and column B. In column C, I'm trying to figure out how to compare column A and B for matches, and display the match in column C, if there is a hit.
I work in a School and need to create a points awards system based on GCSE Grades.
I have predicted and actual grades for each pupil in columns. I need to figure out how I can calculate an award point for this. i.e. if the predicted grade is an A and the Pupil gets a C this would be a minus 1 point score. I need to do this for all subjects and add a total.
I have attached a spreadsheet with a demonstration of what I require. I have created formulas to do what I require however I see it as terrible programming but I cannot figure out another more efficient way.
I am working on a survey analysis and I'm having trouble comparing two columns.
Ex. 100 surveys Column A being age (scale of 1-5) Column B being willingness to pay (scale 1-5)
I am trying to correlate age with willingness to pay by saying =countif(a1:100 = "1" and (B1:B100 = "1" and "2")) Basically checking to see if the 1st age group has a strong willingness to pay; I would repeat with B1:B100 = "4" and "5".
I do backups on a daily schedule. Given the spreadsheet called "A" which is a list of all clients and current backup policy as of last week, and the file "B" which is a snapshot list of servers in our supported environment, does someone have a clever way to determine which systems are in "B" that are not in "A"? The clients are listed in column M in spreadsheet A and this column may have multiple client names listed due to a policy that includes multiple clients in the netbackup schedule.
When comparing collumns to find out if the values are the same or different I use this formula:
=IF((A2)=(B2);"same";"different")In some cases I see the results are not OK. I see the same values but the result states "different". This might be caused by spaces around the value that does not exist in the other cell I think.
File is attached, the green marking show what I expect to see, the yellow marking shows what puzzles me.
So I played around with the macro i posted on the previous thread, two allow me to compare two columns of data to see if they contain the same information, and have come up with the below, which works almost flawlessly:
The idea being that it compares the two rows of data, then in the colmun to the right puts a y or n if the data is contained in both cells. I have added in some code that related to highlighting those cells with 'n' in the allow me to see it much easier. The macro works fine, however it seems that when it runs, it will sometimes highlight random cells not in column d, for example colum g etc.