I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.
I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.
I have tried a few test formulas using COUNTIF function, but with no luck.
I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)
New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
Here is the qtn if two cells A1 and A2 has "ab 1" and "ab 2" the answers for comparing cells should be true as both cells has first 2 letters as alphabets then space followed by numeric..format of cells is same.
The above is an example of two sheets where in Column "C" i need the formula, if sheet1 A Row 1 = sheet2 A row 2 and sheet1 B Row 1 = sheet2 B row 2 then the value should be ture or false.
Why doesn't the IS operator return True when comparing Target to a range in VB when they are indeed the same? Why do we have to keep backing in via rng.Address = Target.Address or Not Intersect() Is Nothing?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) **** ****'_____Works_____ ****If Target.Address = "$B$2" Then ********Application.StatusBar = "To Be" ****Else ********Application.StatusBar = "Not to be" ****End If **** ****'_____This fails_____ ****If Target Is Range("A1") Then ********Application.StatusBar = "A1 sauce anyone?" ******** ****'____But this works_____ ****ElseIf Not Intersect(Target, Range("A1")) Is Nothing Then ********Application.StatusBar = "Alguien quiere salsa A1?" ****End If
End Sub
Again - this is just me wondering why... Am I missing something terribly obvious here?
(Edit) I am guessing it has to do with the Target argument for SelectionChange() coming in ByVal instead of ByRef, but not sure... (End Edit)
I would like compare data using a certain column to key off of and compare whether data has changed.
I have 5,000 rows and 26 columns. Cell C1 is CALLED PMNUM, cell I1 is called CHANGEDATE
I want to find all of the same PMNUMs (C2-C5000), look at the last change date (I2) (sorted in descending order), compare it to the previous change date (I2) and if anything has been changed in columns D2 through G2 and J2 through Z2 compared to the last time the data was reported, list the changes in in AA2.
I'm trying to do a comparison amongst several cells and returning a 1 if true. if D25 is blank or does not equal Yes, Mixed or SP3, and if both F25 and G25 are blank, the output the value 1. First try:
=IF(AND(D21="",D21<>"Mixed",F21="",G21=""),1,"") - This seems to ignore the Mixed and outputs a 1 even if D21=Mixed.
Second try:
=IF(AND(OR(D25<>"Yes",D25<>"Mixed",D25<>"SP3"),F25="",G25=""),1,"") - same as above, if D25=Mixed then value of the cell is 1.
I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Column A Column B Column C Date Number Who 3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) 3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,) ~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~ Column A Column B 902-555-7958Big Bird 902-555-5100Daffy Duck 902-678-0352Elmo 902-555-5752Donald Duck 902-555-7420Bert 980-123-6621Ernie 902-555-2596Grover
I have data exported as .txt from an external program in the following format:
Sample Name Marker Allele1 Allele 2 ....Allele n AAA D8S1358 10 11 XX AAA D16S539 16 19 XX and so on.
Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?
I have a formula in column A of the following sheet. The problem is that when the entry in cell H contains a letter as well as a number then the formula in column a returns 0.
Also if the value for false in the forumula is changed from 0 i.e to 1 or 2, then a value is returned in column A (this is shown in the red highlighted cells).
Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25 D11=John and D12= 40 D14= 45 D16 (answer cell) =John OR: D7=Bob and D8=46 D11=John and D12=48 D14=45 D16 (answer cell) =Bob
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation A2 - Mary Jones, B2 - Silent Auction Purchase A3 - Mary Jones, B3 - Event Ticket Purchase A4 - Gary Jones, B4 - Foundation Grant A5 - Gary Jones, B5 - Internet Donation A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3 A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
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 need to SUM the cells in column J when column E has the word "yes" and the range N:R has a letter "w" in one of the columns. Only one of the N:R columns will have an entry on any one row and it may not necessarily be a "W".
I have Googled, and being a beginner I don't comprehend the result! Some people were talking about using an add in, but I won't be allowed to add software to our PC's.
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.
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 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
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.
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 ?