Put X If Data Has Partial Match

Mar 12, 2012

I have data below, what I am trying to do is to put an "x" if the data has a partial match. This is what I am using below.

HTMLSheet1  ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOL 2Electrical Magnet SchoolELECTRICAL 3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOL 6Abe Lincoln SchoolLINCOLN SCHOOL 7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOL 9Abe Lincoln SchoolLINCOLN SCHOOL 10Abe Lincoln SchoolLINCOLN SCHOOL Spreadsheet

[Code] ...........

What I would like is this

HTMLSheet1  ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLx2Electrical Magnet SchoolELECTRICALx3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLx6Abe Lincoln SchoolLINCOLN SCHOOLx7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLx9Abe Lincoln SchoolLINCOLN SCHOOLx10Abe Lincoln SchoolLINCOLN SCHOOLxSpreadsheet FormulasCellFormulaC3=IFERROR(IF(SEARCH(A3,B3),"x",""),"")C7=IFERROR(IF(SEARCH(A7,B7),"x",""),"")

View 6 Replies


Lookup Data With Partial Match

Sep 4, 2006

My database includes data about period (column A), a product code (column B), Turnover previous year (column C), Trend (column D) and Turnover this year (column E). In column C i want to have the turnovers from a particular product code from the previous year. The difficulty is:

1. that the product code can vary
2. not all periods are availabe.

How can i formulate a formulate that shows the turnover from the previous year, and in case there is no data from the previous year it should show "n.a.". This formula must also take into account that product code can vary. In my example: C3 should show 1296, C4 should show 877, C5 should show 884 and C6 should show 960. In case the turnover of period 200104 was not available C3 should show n.a.

View 3 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.


[Code] .........

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.


[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.


The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Using VLOOKUP To Fill Data With A Partial String Match Separated With A Dash

Aug 18, 2014

I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.

I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.

View 6 Replies View Related

Excel 2007 :: Match Partial Text When Partial Text Is Not Exact

May 20, 2014

This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:

H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP

if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.

So H,AE35(6,9)36+TD could be matched to:

View 11 Replies View Related

Macro That Searches For Partial Match Then Apply IF Statement Based On Match

Sep 21, 2013

I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.

I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet

This is what happens if working right =

look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)

Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)

Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).

View 1 Replies View Related

Match Work List On Partial Match?

Sep 9, 2013

I saw a post with a formula of =LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$A1&" "),KeyWords) which I thought might work, but it returns an error.

I'm trying to search on A1 and return the correct name from a named range called KeyWords.



Chicago IAP

[Code] ......

View 4 Replies View Related

Partial Vlookup Match

Nov 13, 2009

In cells A1:A10 I have the following list;


I want to be able to get a partial match that will allow for spelling mistakes by the user. The wild card (*) seems to only look at the left-most text and return #N/A if the text isn't matched sequencially.

For example ....

View 12 Replies View Related

COUNTIF With Partial Match?

Nov 3, 2008

I have a column with a different alphanumeric strings in each cell. Some of these values have a portion that is common. For e.g.:

Example kealey 1980
Example john commission
Example (hard sell)

Is there a formula to return the number of cells that have "Example" in them?

View 5 Replies View Related

Partial Match In An Array

Dec 13, 2008

column A

cell B1

I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)

View 9 Replies View Related

How To VLookup With Partial Match

Sep 21, 2002

How do you do a vlookup with a partial match?

View 5 Replies View Related

How To Match Partial Numbers

Feb 14, 2012

I have one list of waybill numbers which are 12 digits long and I have another file of waybill numbers which are 15-16 digits long. I have tried a VLookup but I do not get matches on these since the digits are not the same length. Is there any other formula I can use to match a 12 digit waybill against a 15-16 digit waybill number? I am assuming that if 12 digits match in both lists that it is an exact match.

View 4 Replies View Related

Lookup With Partial Match

Dec 14, 2012

I am trying to create a lookup table that will lookup parts of a colum and return the second column in the table. For example, column A has a lot of different titles in it, they each contain different segments and codes in them but I need to see if NM is in the list and if it is I want to return a 1.

Column A Lookup COLUMN Lookup Result
NM01A NM 01
NM02B IN 02

I need to see if what is in the lookup column is in column A, and if it is then I need to return the lookup result.

View 6 Replies View Related

Lookup Using Partial Match

May 2, 2014

i need some formula to lookup with partial match "name", like this below :

property code
partial name (manual type name)
desired result
jim bob
jimmy bob



it's possible with excel formula?

View 3 Replies View Related

Partial Or Posible Match

Jan 10, 2008

select first the 2 ranges i want to compare
select the columns used like criteria

compare and find the posible match and copy on an new excel the result

possible matching


comun value 28244654



View 9 Replies View Related

Partial Match In Two Cells (text)

Jul 11, 2014

How to check for a partial match looking in one cell, A1 for example, and checking to see if the contents of B1 show a partial match (example attached).

I can't use the vlookup because I only want to look at cells specifically, not a table/list. Please also note that the attached example has no formulas.

Forum Example.xlsx‎

View 5 Replies View Related

Assign Value If Partial Text Match?

Mar 28, 2013

I have a long list of categories in the column A (seen below). To this list i want to assign keys that will support with further calculations (in a column B).

An example of such a key would be in the case of the given example "ACTION SPORTS" and "FOOTBALL". The whole list of these "keys" hast 17 elements.

It is evident that i will need some kind of a partial match. The one i have found on the internet, limittin itself just on the first or last number of characters wont work.

Would it be possible to select my "key - list" (all 17 of them). Then excel would asign a key, whenver it would find a partial match, matching the key?

The Data list (Column A):


View 6 Replies View Related

Partial Match Boolean Type

Nov 24, 2009

I have strings in cell a1. I will put a formula in a3 if string "xyz" is found inside a1, it will show value from a2, else shows nothing.

i tried simple formula cell a3
here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?

View 9 Replies View Related

Lookup By Partial Text Match

Apr 9, 2008

consider the following master data

1AB Cat
2HB Dog
3BX Rat
1ABC Tiger

Now I have the following data, which will lookup the master data presented above and return the corresponding animal - For eg.

1AB29489284 CAT
1ABC0395935 Tiger
2HB29492492 Dog

The above CANNOT be done by VLOOKUP, since the class - Tiger, needs to be differentiated from CAT by the 4th letter. How can the above problem be solved???
(This question recently came in one of the Recruitment Exams)

View 3 Replies View Related

Lookup Partial Match Between 2 Workbooks

May 2, 2008

I have a spreadheet which requires data from another spreadsheet. Spreadsheet 1 is only project numbers, spreadsheet 2 has all the customer data in it. I have used a 'vlookup' function to find the project number and copy the appropriate information over. The problem I have is the projects are a 6 number format (eg 531300) in both spreadsheets. In the sheet I want to complete, the project numbers only match the first 4 numbers, as they are seperated into sub-projects. Therefore the data in the data spreadsheet will only have a row for 531300, yet the one I fill in might have rows 531300, 531301, 531302 etc. Is there a way to run the 'vlookup' and only match the first 4 numbers. The current formula I have in the first cell is as follows: =VLOOKUP(A4,'[Project Database_Updated 04-30-08.xls]Sheet1'!$A$1:$E$1007,2,FALSE)

View 3 Replies View Related

How To Delete A Row Based On Partial Match In Column

Jul 19, 2013

I am using VBA

I have an input box pop up to ask the user for a base item number. The way the excel sheets part numbers work for example is TM-T88VP-GRY, the last part 'GRY' changing based on the variation of the item that someone ordered. So, the base item number would be TM-T88VP, and what I want to do is search through a set column (Column G) and delete any row that contains that base item number.

View 4 Replies View Related

Formula To Match Partial Text String?

Jun 3, 2014

I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula

=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)

However, it works with some text but won't work for some.

For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.

View 2 Replies View Related

Finding Partial Text - In The Case Of More Than One Match?

Feb 20, 2014

I'm looking to use Excel to finding partial text In the case of more than one match

I used the following formula in cell C12:

=IFERROR(INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""),COLUMNS($B12:B12))),"")

But it's work only 100% match word not by partial text.

View 2 Replies View Related

Indicating A Partial Match When Comparing Two Lists

Aug 15, 2013

From the attached file.
Column 1 is a file name
Column 2 is a list of file names (but some of the files have been split into -A and -B parts or suffixed with an A or B

I need to highlight in column B, if the exact file name in column A exists

Column A: BSDS-0001
Column B: does not have this exactly instead it has BSDS-0001-A and BSDS-0001-B

I need some indication/highlighting that BSDS-0001 does exist on column B

So an indication that the whole of the file name in column A appears partially in column B. I have tried using max character lengths searches etc but cannot get an accurate way of doing this.

View 11 Replies View Related

Delete Rows Based On Partial Match

Aug 11, 2007

I currently have a speadsheet that looks similar to the following:

SVRW_67 784598 475395u
SVRW_34 fdsjsjdf 734978
CAT_56 ghdrhad gaghadh
CAT_67 48578 8943539

I'm trying to create a macro that will delete all rows with "CAT" in the REF column.
I've searched this site and struggled trying to adapt other methods listed and got nowhere.

View 3 Replies View Related

Delete Columns / Rows By Partial Match

Feb 19, 2008

i would like to find code for a macro(s) to delete selected columns and delete row by partial string..

due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..

View 8 Replies View Related

Flag Partial Match Where Cells Contain Key Words

Jun 2, 2008

I have a list of customer names. These include one-off customers and companies. Unfortunately, they are not always keyed correctly and in the same format. What I need to be able to do is flag any companies. These will be those that contain key words, such as Ltd, Ltd., Limited, PLC, & Co, & Sons and so on.

My customer list may look like this:

1. Bill Jones & Son
2. Mr B Smith
3. Posh Homes Ltd
4. Posh Homes Limited
5. Mr A Singh
6. Bill Jones & Sons

I then need to flag no.'s 1, 3, 4 and 6 as Companies while 2 and 5 are flagged as non-company.

View 6 Replies View Related

VLookup - How To Find Partial Match Within Larger String

Apr 9, 2014

I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time

Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No

Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?

View 5 Replies View Related

Search Variable Number Of Sheets To Find Partial Match On Name

Jun 19, 2014

I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)

Tab = Jan 14
Fuji apples3


View 2 Replies View Related

How To Apply Categories Based On Partial Text Match Like Vlookup

Jan 17, 2013

I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.

Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.

What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.

Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved