I have a database of 6 digit numbers in one column(let's say column A). I would like to put in a 6 digit number in a cell (b1)and have another cell (c1) give me a response as to whether that number exists in the database (Exists or Doesn't Exist).
View 2 RepliesI have the following data set:
1
Test
2
Testing
3
Test 3
4
Test
Exists on line 1
5
Test 5
6
Test 6
7
Testing
Exists on line 2
So basically I'd like to know if there is a duplicate line item what line item the duplicate actually exists on. Is there an easy way to do this with a formula?
I am trying to make a spreadsheet for bank reconiliation. I found this formula, =if( countif($B1:$B$1000,D1),D1)=0,D1,"") but there are two open parathese and three close. What needs to be corrected?
View 3 Replies View RelatedI'm sure this will be an easy question, but I've been searching for awhile and can't seem to find an answer. I'm trying to write code in VBA that will check if a directory within c:documents and settings...my documents exists, and then save a file to that directory. The problem is, I'm having trouble finding the my documents directory. Is there some object or property that I could use to find the my documents path? Note that the worksheet that I'll be working with is in a separate directory, and therefore neither activeworkbook.path nor thisworkbook.path would be an option.
View 3 Replies View RelatedI have programmed an Excel file that removes all the standard commandbars from Excel and then opens a custom commandbar, I built, with limited functionality ( Named "Limited"). When I open this file on another computer, since that custom commandbar does not exist in that copy of Excel no Limited commandbar shows. So I created a macro that builds the custom commandbar when a file is opened. What I want is when the file is opened on subsequent times that it checks to see if that commandbar already exists. If it does, it skips the macro which builds this custom commandbar for the first time. It then disables the standard Excel commandbars (code written already) and enables the "Limited" commandbar (code aleardy written).
View 2 Replies View RelatedI have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:
A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE
Before my workbook is open the following code is run, which asks the user to enter the password. Depending on the password Range("name").Value takes values from the worksheet "Data" from table "M4:M20". The list of passwords is in the table "L4:L20". My code seems to be not optimal (too long). Does anybody know how to optimize this code?
Sub GoodMorning()
On Error Goto Error
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password", _
"Enter Password")
Case Is = ""
Call HideSheets
MsgBox "Sorry, that password is incorrect! Please contact the administrator.", _ ............
Im trying to write a formula that will check if a name that I have in column A also appears in column B and if it does I want it to return a value for example TRUE in column C.
I'm currently doing a survey and I have all employees in column A (600persons) and then column B I enter thos that have taken the survey. So If an employees name exist in column B he/she has done the survey and then they I get the value "TRUE" or "1" or something else. So in this way I can easily see who has and who hasn't done the survey. I've been trying with VLOOK and so on but I can not get it right? Maybe I need VBA?
I require a row of details to be copied to another worksheet by typing in a unique ID using a macro so Sheet 1 is a data base of items (every item have a unique code like 1001, 1002 etc) and sheet 2 has a table, and next to the table is a cell, which i need to work like a search engine.
i need to be able to type the unique id in a cell, in sheet 2, then click an Add command Button. This button then finds the unique id in sheet 1, and copies all the items details in the same row, into sheet 2 in the table, then i require the search engine to be cleared for the next item to be added. (Assumed Experience:Below Average, I know few formulas and know very basic macros)
I am attempting an If Or formula and keep retuning a #Value error, I can't for the life of me figure out why.
This is the formula that I have written.
=IF(OR(H44,H45,H46,H47,H48)="Other:","True","False")
Cells H44,I44,J44 have been merged into one cell (H44), and the same for 45,46,47,and 48 - Could this affect the Formula?
I have a huge database consisting of zipped file names which i created by importing the directory listing into excel. The zipped files contain two files each an mp3 and a correspondent cdg file (karaoke files).
I continue to add new disks (as I acquire them) to my collection. the trouble is that new disks have some songs already in my collection and I do not want to waste space in hard drive with duplicates. The existing data is arranged in a worksheet by columns as follows:
Discnumber | track number| artist | song name | path | complete file name
what I would like to do is to check the ** listing after import it to excel in the same format (i can do this part ) and then check it against the 10,000 plus entries that i have if the artist-song name combination already exists in the data. If so return the row in a separate worksheet or pivot table so that i can check it. this way I would only save the songs I do not have in my hard drive thus avoiding duplicates and saving precious space.
I have a list in a column and just want to count how many of each entry exists and place that number in another list:
Column A Column B Column C
abogado 4 abogado
abogado 7 mammamia
abogado 8 trifecta[code].....
An image is here: 12.23.2013-13.59.36 - joezeppy's library
I want to create a table that lists the number of entries for each cell in column a and then lists the entry next to it. Can I use a pivot table or something?
iam trying to get a messagebox to notify the user that the text he has entered into the userform textbox is already in use in a sheets column. this is what i have been trying to get to work
Private Sub txtID_Change()
If Sheet3. Range("a8:a1000") = "B" & txtID.Value Then
MsgBox "Text already in use, Please use different text"
End If
End Sub
I have posted about creating an Excel worksheet which would help determine proper entry into a retirement plan. Another idea that I had entailed setting up a separate worksheet which would return the dates of entry following an employee's date of hire. Therefore, an employee hired on 8/5/2005 would have dates of entry following that stand at 1/1/2006 and 7/1/2006. Then, I had an idea of creating a formula or function to add up the number of hours that the employee had worked.
Setting up a separate worksheet with the number of hours that an employee worked during the initial eligiblity computation period and the subsequent computation periods (separate respective columns for the initial eligibility computation period and then for each subsequent computation period) would help. I would calculate using sum functions.
So, to sketch this out:
One sheet would have the employee's DOH
Another worksheet would have the Dates of Entry subsequent to that DOH (e.g. for an employee hired 8/5/2005 the next Dates of Entry entails 1/1/2006, 7/1/2006, and 1/1/2007)
Yet Another Worksheet would sum the total hours that an employee had worked since DOH on a month by month basis (i.e. the total number of hours than employee had worked since his or her DOH up to a certain point on a monthly basis; e.g. an employee hired on 8/5/2005 would have on the sheet the calculation of the hours this employee had worked as of from 8/5/2005 to 9/1/2005, then the next column would have the total the number of hours worked by the employee from 8/5/2005 to 10/1/2005, etc.)
Finally, the worksheet with the DOH information would have a column which would (this would probably entail heavy use of VLOOKUP) snag the information as to how many months and years an employee had worked as of the subsequent Dates of Entry; if the employee had worked 1,000 hours and 12 months as of 1/1/2007, for example, the employee would enter the plan.
To explain the situation further:
The employee must work 1,000 hours during his or her initial eligibility computation period. That starts on the day of the first hour that an employee works for the company. So, an employee hired on August 1, 2005 who worked one hour on that day must 1,000 hours from then till August 1, 2006 to enter the plan as soon as possible. If the employee did not work 1,000 hours during that period, then the eligibility computation period shifts to the plan year. So, if the employee did not work 1,000 hours between August 1, 2005 and August 1, 2006, but did work 1,000 hours between January 1, 2006 and December 31, 2006 (this of course presumes the plan operates on a calendar year)
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies View RelatedI have 2 sets of data in my excel spreadsheet.
Column A has a bunch of numbers and some duplicates
Column B has the same numbers but lots more and no duplicates
Column C describes what column B number does
All I want is for my formula to return the value in C when A#=b#:b#. The if statement, that I can't figure out how to get it to work right is this. =IF(A2=B2:B1021,C2:C1021)
This is fine if the 1st value is true (meaning a2=b2), the problem is the IF statement returns the 1st value in the array(I think) which in this case is false. An example of the array is below, the result of my formula is always false unless i change B2 to equal A2.
00E200002-Way Mir
00E200012-Way Mir
00E200022-Way Mir
00E200032-Way Mir
00E000E22-Way Mir
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
to creating formulas in Excel!
Basically, all I want is for a formula to tell me if I have matching data in two separate cells. What I have is data entered in cell A1. What I would like is one single formula to check if the same entry has been entered in cells A2:A10 and if so, return a 1, and if not return 0.
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!
View 2 Replies View RelatedSo I have this formula to basically lookup the letters in column L and if certain letters exist, then change those letters to another letter. Here is my Formula:
=IF(L1="A",IF(ISNUMBER(MATCH("B",$L$1:$L$200,0)),"B",L1),IF(L1="D",IF(ISNUMBER(MATCH("E",$L$1:$L$200,0)),"E",L1),
IF(L1="L",IF(ISNUMBER(MATCH("L",$L$1:$L$200,0)),"O",L2),IF(L2="F",IF(ISNUMBER(MATCH("K",$L$1:$L$200,0)),"K",L1),L1))))
For example: if column L looks like
A
B
A
E
D
Then it would convert the A's to B's since there is a B present and D's to E's, but if the B and E hadn't been in the equation then it would leave the A's and D's as they are. WIth this formula the output looks like:
B
B
B
E
E
My problem with this formula is that it only recognizes the first letter in each column. Sometimes I have multiple letters in a column and I need it to recognize them and change accordingly.
For example:
A
D_B
E
I would like to have it change to
B
E_B
E
I have attached a sample workbook that may make it more clear... If a VBA code would work better then I would be open to that as well. Combining question.xlsx
So I have the first part of my equation and it works fine. The equation is in Column D.
=IF(ISERROR(FIND("Van",A7,1)),"",B7*C7+50)
What I want it to do is: Combine the above for Col D with: if the Unit Name Column (A) says anything EXCEPT Van, I want it to multiple Cost (Col B) by QTY (E) and put the total in Col D.
i need a formula that tells me if a value exists in a column. for example if there is a value in a cell in the b column of 23 no matter where it is, i want cell c25 to return the word "yes".
View 2 Replies View RelatedI am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.
Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".
I am currently using this formula to count dates between 2 date periods If specific condition exists.
=COUNTIFS(
Data!S1:S100000,"*KP*",
Data!X1:X100000,">=10/1/2010",
Data!X1:X100000,"=10/1/2010",
Data!X1:X100000,"
formula I can use to determine age between date of birth (in one cell) and an event date (in another cell)., i.e. birthdate 3/15/2000 with an event date of 3/2/2007.
View 9 Replies View RelatedIf I have A1:A1000 in a column, and I know that A1000 is the sum of 3 cells within A1:A999 but not sure which, what kind of functions can I write to trace out these 3 cells?
View 8 Replies View RelatedI need to take a specific action when a cell has an actual formula in it versus when it just has a "value". Is there a procedure or command which will allow me to identify if a certain cell has a "formula" (like =sum(a1: a5) ) or just a value.
View 3 Replies View RelatedI am trying to determine a formula to know a period time for example i started to test a unit at 10:20 am of 10/02/2011 and i would like to add 52 hours of test and i would like to know when and at what time will finish the test?
View 3 Replies View RelatedI would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:
Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%
each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.
I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.
Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?