I have the following data (as below).
Basically I want to check if a value is in AC but not AD, I would like to mark it as such in column AE i.e. mark all datevalues in column AD that aren't in column AC.
Could anyone please help with the required formula?.....
What is the simplest way to do it in VBA?
View 3 Replies View RelatedI have uploaded an example file and I need a formula to sum only the entries that are listed on the lookup sheet which match the contract name.
In order to do this the formula will need to look in column G and if there is a match on the lookup sheet then sum column Q. It would also need to match the contract name column U with Column B
I have provided an example of what I need as my description is not very clear.
The formulas would be required in the pink cells.
I am trying to get the list of all files contained within a directory and have them displayed in a confirmation box, listed one beneath the other (as a column, each file name in a new row).
View 2 Replies View RelatedI have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
I've two lists of text - one long with multiple words and one short with one or two words. (sample data: [URL]
I would like to check if List A has the word(s) present in List B, and return a Y/N accordingly.
I have a worksheet in which one particular cell contains a certain formula. For security reasons, I wish to hide the FORMULA contained in the cell. However, the VALUE of the cell given by the above formula should always remain displayed for the user.
I want this to be done only for that particular cell. I tried hiding the formula bar,but it hides it for all cells.
I'd like to display a formula contained in a cell within the worksheet. How can I do that?
View 4 Replies View RelatedI am trying to write a formula that will return a statement if a certain month is contained in the text within another cell. Formula is =IF(ISERROR(SEARCH("Dec",Assumptions!B2)),"Ensure Journal is Non Reversing","")
Cell B2 contains a date in the format of Dec 08, so if this date contains Dec, then return "Ensure Journal is Non Reversing", if it doesn't then leave the cell blank.
At the moment it is putting in the first test for every month I select and not changing to blank.
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
View 2 Replies View RelatedI have an excel sheet with 30 names in column A and I'm asked to do the following:
Write a procedure – a sub – that uses an input box to ask for a name. The procedure should then scan through the names in the list to search for the name and make a message box to state “X is not in the group” or “X is in the group”, where X is the name from the input box. HINT: You can make a variable “found” that starts with the value 0 and gets the value 1 if the name is found in the list.
I have two very large data sets and I need to see if any of the values on one sheet (B2:B380975) appear on another workbook (B2:B216607).
I know that I can do this with COUNTIF, but it's going to take ages to calculate.
Current COUNTIF formula is:
=COUNTIF('[ORIGINAL.xlsx]data'!$B$2:$B$216607,B2)
Is there a way of checking for a list using VBA? We have a reporting frame work for another application that outputs into Excel, but I have been asked to come up with a way of converting this to XML format.
What I need to do is check for a list named "v10List" on sheet 2 of the selected workbook to make sure it is a file sutible to be converted. Just incase there is any confusion (as there was with me!) it is not a named ranged but a named list.
I have created a list for Cells A2:A10 which has the various roles of person. Gave it a name Designation. I have created a drop down list using this list by going to validation. =Designation
I want a function that would check the value entered and search this list would return TRUE or FALSE
I want to write a formular to initiate an action if a particular text is not in a list For example, I have a list in column D1:D50, and I want the following to happen in say cell A1
If "apple" is not in list D1:D50, then in put "N" in cell A1, else input "Y".
In VB is there a way to check the values in a combo box, to check whether the value you are trying to insert into it exists in the list, if not add it to the list.
View 3 Replies View RelatedI want to make a drodown list with check boxes to select more than one choice ?Book5.xlsx
View 4 Replies View Relatedhow I can check if a cell contains part of a value from a list? Do I need a formula or VBA? (Both would suit)
Example:
A1= BLABLA Apple BLALBA
A2= BLABLABLA Orange BLA
List in C1:
C1= Orange
C2= Apple
If it contains one of the word, I need the function to return the word...
What I have tried:
Search formula
Match formula
VBA with "If...Like" statement
Keeping in mind that the key word Apple/Orange in A1 or A2 can be located anywhere in the "sentence" (could be after BLABLA or BLOBLOBLO or BLU etc...)
This is something I would then apply to a range (I suppose if it requires VBA, then with a loop which I would be able to manage)...
EDIT: Cells in A1/A2 will never contain two different values from the list (it is either Apple or Orange)...
I need a formula to check if anyone from the Old employee col(B) exist in current employee column(A).
A B
Current employees Old employees
A C
B G
C T
D
E
F
G
dim MyRange as range
dim Col as integer
Set MyRange = ActiveSheet.Range(2,1), Cells(102, 1))
if application.worksheetfunction.Match(ActiveSheet.Cells(5, col),MyRange,0) > 1 then
' it was in the list.....
else
'it is not....
end if
I imagine this isn't the best way and I since I couldn't get past the Set statement. What am I missing? Is the range of dates causing the problem? If my range is one cell in the range it sets the range?
I would like to create a form to select some columns from a list of columns.
This could make it simpler for an end-user to chose which columns they want to see in my application.
(see my previous question: [url]
Ideally, I would prefer to do that just like rows can be selected in an Excel list.
However, I doubt this could be done.
So, to be practical, I would put a button on my sheet.
When the user clicks the button, a form would appear and show the current list of columns.
The user would mark or unmark to define his view and click ok to get the sheet with only the columns he wants.
The number of column in the table can change.
When the form loads it must determine what are the columns to include in the list (headers).
What I really don't know is which controls I should use on the form?
Are there some list control with tick marks available, for example?
Or, I might dream of a mark-enables combo box or list box, as can be seen often on the web.
What I would like to do is have a macro check a column for any occurrence of a few different numbers, and then enter the text "n/a" in a different column of each row that one of the numbers doesn't appear in.
I have a list of part numbers that require a certain operation, and I'd like to mark off a cell for each item that does not require that operation. I've attached a simple spreadsheet that should help explain this better.
I have several series of 13 figures (from 0 to 20), such as :
000141833000001
I would like to filter those series with the following condition :
Each figure (except the zeros) should be higher or equal to the first figure (found on the right) which is not a zero.
In the example :
14 should be higher or equal to 18 : FALSE
8 should be higher or equal to 3 : TRUE
3 should be higher or equal to 3 : TRUE
3 should be higher or equal to 1 : TRUE
So, the serie is FALSE, due to the fact that 14 is not higher than 18.
I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid.
The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....
I have a list of abbreviations in Sheet 2 and Names of Companies (Full name of Company and short name) in Sheet 1.
What I need to do is check if the abbreviation used in the Short Name Field in Sheet 1 is correct based on the given abbreviation list in Sheet 2.
here is the example:
Sheet 1:
Fulll Name of Company
Short name
AMERIPRISE FINANCIAL, INC.
Ameriprise Fin
Sheet 2: Abbreviation List
Full Version
Abbreviation
Academic
Acad
Bank
Bk
Financial
Finl
in the abbreviation list the correct abbreviation for financial is Finl so the short name should have been "Ameriprise Finl" and NOT "Ameriprise Fin". I'd like the rows highlighted in yellow if it is incorrect.
I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.
So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.
This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)
IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)
I have been playing around with this for a bit and now have it sorted out. This is a macro that will check a name list and highlight any cell that contains one or more of the names.
View 3 Replies View RelatedHere below the case:
List1 in Sheet1:
TEST1
TEST2
TEST1
TEST3
TEST8
List2 in Sheet2:
TEST1
TEST2
TEST3
TEST4
TEST5
I would like to know if it is possible to have in a single cell the information about the presence of an error in the List1, because a cell of List1 got a value that is not in List2.
I mean all cells in List1 have to contains the same values of List2. It is like a data validation. But it would be nice to have a single cell with "There is an error in your range".
I have a sheet which contains a list of products. I have created a check box next to each name with the linked cell in the adjecent column. I have formatted the cell so you can see the TRUE or FLASE.
I need to be able to select different products and then when I run a macro, it places all of the selected products onto the second sheet. I just need it so it lists the products in column A with no blank rows.
Can this be done using Index/Match? Would a macro which achieves all this be possible?