Check Cell Contain Part Of Value From List?

Aug 5, 2014

how 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)...

If Statement To Check Part Of Cell Contents

Apr 4, 2007

IF statement that looks at a cell to see if it contains a word.

ie IF A2 has " Total" in it then do this

Multiple Lookup: List Of Part Numbers Based On The Product Part Code

Feb 12, 2009

I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

Macro To Check Part# And Sum

Apr 13, 2006

I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number.

Check Open Workbooks By Part File Name

Jun 27, 2008

the Data Grabs and pastes perfectly now but the Macro copies the data from the open file - the problem is that every day the filename (eg stats_01_02) changes according to the date so what I want to know is how to have the macro copy the data from an open file using just the first part of the filename.

Check If Abbreviation Used In Cell Is Correct Based On Given List?

Apr 24, 2013

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
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.

Check A Cell For List Of Data - If Found Then Print Row

Mar 29, 2014

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)

Name Lookup: Check A Name List And Highlight Any Cell That Contains One Or More Of The Names

Jan 20, 2010

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.

Lookup Value In A List Within Part Of Text Value In Another List

Feb 21, 2012

I have two lists.

The first:

P1116
MC1070
BD1068

I want to check if any of these values are contained in the text of each item in the second list, that looks like this:

64K4014-4" , 64K1030-4"
64P1116-3"
64P1187-4" , 64p1117-4"
64P1330-2"
64P1405-2"
64MS1007-0.75", 64MC1070-2"
64K1062-3/4"
64K1061-0.75"
64CW1062-2", 64BD1652-2"
64NG1007-1 1/2"
64K1008-2", 64BD1041-1", 64BD1068-2"
64HS1002-6"
64HS1002-6"
64CW1068-2"

I tried using the =search() function in an array formula, but I can only get it to work for a single value in the first list.

Multiple Applications Combined Into One Part Number With A List Of Applications In One Cell

Nov 28, 2013

If the admins or you would like to change or recommend a change, I am trying to figure out what the code would look like for cells B22:B28. I need it to count the number of occurrences of the part number in A4:A17 and then combine the applications from the corresponding cells in column F (F4:F170 into a single cell (B22:B28).

On sheet 2 I would manually copy/paste the list of part #s from sheet 1 and remove duplicates. The add the formula that I can't figure out into column 2.

Creating Spot Check List From Inventory List

Mar 17, 2014

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 .

Max Date Only From A List Of Part

Jan 19, 2010

I have a list of about 200 parts that shipped. However, each part shipped several times over the last year. I probably have 3,000 - 4,000 lines total.

What is need is each part once, with the last shipped date.

List Names, InputBox, Check If Name Is On The List

Sep 7, 2009

I 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.

Check If Value On One List Appears In Another List Without COUNTIF

Mar 3, 2014

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)

Check If Text String In Cell With Other Text Is In List

Mar 27, 2008

I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?

There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.

VBA Check If A Cell Is Empty - Move 7 Cells Over And Check Again (Loop)

Aug 10, 2012

I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.

Code:
Sub Tester()

Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range

Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)

[Code] ......

Create Auto Updating Price List Using Part Number And Website

Jan 3, 2014

I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.

Website: WebFLIS - Public Search
Sample data Category
Chaplain Kits
Item
Chaplain's Kit, Consumable
NIIN
9925-01-326-2855

Price
\$276.94
Description
Also called the ReSuppply Kit

VBA Code that allows me to automatically open the page.

Dim IE As Object
Sub submitFeedback3()
Application.ScreenUpdating = False
Set IE = CreateObject("InternetExplorer.Application")

[Code] ......

Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).

If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.

Check For A Named List

Nov 24, 2008

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.

Function To A Value In Check A List

Feb 8, 2010

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

Check A List For A Text

May 4, 2009

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".

Check Value Is In ComboBox List, If Not Add It

Oct 18, 2006

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.

How To Make Dropdown List With Check Box

Jun 27, 2014

I want to make a drodown list with check boxes to select more than one choice ?Book5.xlsx

Check Data From Existing List?

Dec 4, 2013

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

Formula To Check ID And Timestamp Against List?

Dec 16, 2013

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.

Formula To Check If One List Contains Text From Another One?

Dec 21, 2011

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.

In A Sub I Need To Check If A Date In List Of Dates

Feb 3, 2007

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?

How To Create A Check List Form

May 17, 2008

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.

Formula To Check Whether Value Is Contained In A List

Aug 12, 2008

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.

Check Column Against List Of Numbers

Jun 24, 2008

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.

Check If The Values Of A List Are In Progressive Order

Jan 18, 2013

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.