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)
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 a sheet which details specific card numbers in Column A, and the date and place of transaction in the Column B. This very long list contains multiple entries for each card number. What I would like to do is use Sheet2 to list each card number and the number of times it appears in the list.
View 4 Replies View RelatedI have a list of numbers in Column A (labeled "Number"), with a list of dates in Column B (labeled "Date & Time"). Every date in Column B is unique, but some numbers in Column A appear more than once.
I am trying to create a simple formula that will automatically search for the LAST time a unique number appears in Column A (ignoring all previous instances of this number), and copy the date right next to it in Column B to another location on another worksheet.
I tried fussing with several of the built-in functions, but I really don't know what I'm doing, and the built-in help documents are filled with too much mathematical/programmer technical terminology for me to understand. All the tutorials I've looked at seem to think I want to add some numbers together, but I don't want to do any calculations!
I'm trying to do a vlookup on a list of items where the item I'm looking up may appear more than once. when this happens it will only post the result of the first one it comes to in the list when I use the standard vlookup. does anyone know of a way I can get it to report all the results for a value that appears more than once?
CodePO Line DescriptionQty DueUOM
745000738031015mm Sandstone 3,018M
745000741791015mm Sandstone 3,018M
745000745091015mm Sandstone 3,018M
1245000738032015mm Jet 3,018M
1245000745092015mm Jet 6,036M
4645000741792015mm Poppy 3,018M
4845000741793015mm White 6,036M
4845000745093015mm White 3,018M
I have attached an example spreadsheet that shows my problem. Basically, in column A I have several thousand rows of 3 or 4 letter codes. In column B, I need a formula that will find one particular target code in Col A and then in the adjacent cell in Col B, display the first code to appear in Col A above the target code which matches the list of desired codes.
So the briefly re-state the problem, I need a formula which finds every case of one particular code in Col A (in the example spreadsheet the code is ABCD), and then read back up Col A to find the first value which matches the desired code (from a list of about 5-10 codes), ignoring other values which represent codes that are not on the list, and place this col B adjacent to the target code.
I've tried to sort a list using the sort 1st by and then by options, but it will only sort by one of the options. I've tried with a header row, without a header row and every way I can think of and still can not get it to sort by more than one column on this particular list.
View 6 Replies View Relatedthe number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
is it possible to do the following with a drop down list, without requiring VBA? I would like to have 20 products to choose from in a drop down box. When the user selects one of the products, a more detailed product description will appear in the same cell where the drop down list is. For ex: If the user chose "chair" from the drop down list, what I would like to appear in the same cell is "chair - model T654".
View 2 Replies View RelatedWe are in the process of switching from running Excel 2007 under Windows XP to running Excel 2010 under Windows 7. Most of my Excel reports are not having any issues. However, I have several macros that cause Excel to crash when they go to save the file. Through trial & error, I have found that the problem seems to occur after deleting custom lists that were created earlier in the macro. Here's the pertinent code:
' Declare Variables
Dim VarListNum1
Dim VarListNum2
Dim VarListNum3
[Code]....
When I execute the macro line-by-line, it is creating & deleting the custom lists as expected. However, if I try to save the file after any of the custom lists have been created, Excel crashes. Has something changed in Excel 2010?
I have a list of 562 records. In order to generate a targeted mailing list, I would like to display) only those records meeting the criteria:
=SUMPRODUCT(--(M2:M562>0),--(U2:U562>0)) .
I need to lookup & count the number of cells from column I to BH with values greater than 0 in sheet 2 and return the results to the corresponding list of items in sheet 1. However, the data in sheet 2 have duplicate list of items and may have duplicate values as well from Column I to BH which I wanted to be counted as 1 only. I'm attaching a file as a reference.
View 3 Replies View RelatedI'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View RelatedI want to count the number of times any given number appears either as a consecutive group or singularly.
To give you a context I monitor windturbines and for any given fault code I wish to count the number of events it occurs in a month. Now it could be for 1 hour then clear the next then back for 17 then claer again. That would be 2 events!
NB the data is in seperate coulumns per turbine.
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 RelatedWhat formula will count the number of times the value J3 appears in E2:E400. BTW...the a-g is formatted as a table. excel 07
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
I 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.
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.
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 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 am trying to extract the number of times a name appears on a certain range of cells on a worksheet, now I can get this to work, but I am sure there is a better way I just cant get it to work!
=SUM(COUNTIF(sales1!$B$8:$F$11,A18),COUNTIF(sales2!$B$8:$F$11,A18),COUNTIF(sales!$A$8:$B$11,A18))
I have only used 3 pf the worksheets, but in time there could be up to 30.