Cell Return All Matches From A List And Have The List Of Matches Reduce As You Type

Jan 28, 2009

is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?

View 3 Replies


Return Matches & Non-Matches From Delimited Cells Against List Range

Jan 29, 2010

In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":

1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List

2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List

3.) Report In Column D: Identify and return all unique values in "Column C" as a List.

Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"

View 4 Replies View Related

Return A List Of Unique Matches

Nov 27, 2006

I need to return all the unique matches from a set of data.

For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.

Example of data set I need to lookup in:
prod 1 venue1
prod 2 venue 2
prod 1 venue 3
prod 1 venue 10
prod 2 venue 6

I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.

It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....

View 10 Replies View Related

Cross Check 2 Ranges For Matches & Return Row Number Of Matches

Apr 29, 2009

I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK

View 3 Replies View Related

Macro To Jump To Cell That Matches Text Selected From A Dropdown List?

Mar 24, 2014

I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.

View 4 Replies View Related

Search List For Matches From Another List

Jun 25, 2008

I have a chart of accounts that is probably 30,000 accounts long. I have another list with say 500 accounts. I need to check to see if the 500 accounts exist anywhere in the 30,000 accounts. The next step is, the 30,000 accounts is separated into different categories, so say it's 30 blocks of 1000 accounts. I need to check the 500 list in each of the blocks. I'm just trying to see which accounts are missing from the 30000. So if they exists in each block then all is good, if they don't, we have problems.

View 3 Replies View Related

Go To First Cell In List That Matches Contents Of Active Cell

Feb 20, 2007

I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...

I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).

I sort the list so that the all names that are the same (ie John Doe) are together.

A short example list looks like this:

Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz

In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).

Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.

Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...

View 9 Replies View Related

List Matches In A Table?

Aug 12, 2012

I have a large table that gives me the intersection points of many lines. I have used conditional formatting to highlight the intersection points that match by turning the text red, thus showing me when 3 or more lines intersect. Manually scanning the whole table is inefficient as I wish to expand it.

Due to size limitations, I've uploaded a shortened version of the workbook, just a copy with one example of a match.

How do I get excel to locate the matches (currently highlighted with red text) and list them in column I "Intersections" all neatly sorted?

I thought Advanced Autofilter but I don't think the criteria range can search for red text alone.

View 6 Replies View Related

Macro To Copy Row If Matches List?

Nov 15, 2011

Macro that would copy a row if the value is found in separate list. For example, in column B of sheet "223" I have values. In another sheet "DATA" I have a list of values, which if these values match the value in column B of sheet "223" it would copy the entire row from "223" to another tab "output".

View 9 Replies View Related

Lookup Max Which Matches A Time In List

Feb 13, 2008

i am trying to do a lookup on a data column (B) which contains time data on a worksheet which is a constantly growing list from a DDE data link macro- the data i want returned is in column A,

so for example

column A, column B

0.8970 13/02/2008 21:58

0.8965 13/02/2008 21:58

0.8990 13/02/2008 21:58

0.8995 13/02/2008 21:59

0.8982 13/02/2008 21:59

0.8983 13/02/2008 21:59

i am looking for the highest and lowest values where the time matches a certain hour of the day, so where another cell on the worksheet 1 equals 22, do a lookup on all the rows which is in time 22:00 and return the max no in column A (0.8983),

and the min in another cell(0.8977)

think my limited excel skills are not up to it i'm afraid despite searching through the forums and trying out a few lookup and max combos as array formulas.

View 9 Replies View Related

Search A String For A List Of Matches

Nov 30, 2006

I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.

View 9 Replies View Related

Compare 2 ListBoxes & List Matches In A Third

Aug 13, 2008

I have two excel sheets, one loads up in Listbox1 and the other in Listbox2, i would like to be able to see if any entries in listbox2 match with entries in listbox1 and then to display the matches in listbox3 with a click of a command button. Is this possible? If so, how would i go about it?

View 3 Replies View Related

List All Possible Outcome Combinations For 50 Football Matches

Dec 20, 2012

An example will be as follows. List all possible outcomes for 3 matches. That will be 27 possible outcomes.

I would like results for my request of 50 matches to be displayed as follows.


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


Is there a way i can have the possible outcomes listed as above for the outcomes of 50 football matches? I do know that the outcomes will be hundreds of millions if not billions.

View 9 Replies View Related

Compare Data-set With List & Summarize Matches

Jul 16, 2009

1) Background Info

We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.

The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.

Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)

2) What We're Trying To Do

We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)

3) The Problem

Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:

a) open the attached workbook, and go to the worksheet named "Data"

b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)

c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name

My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.

How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.

To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.

View 8 Replies View Related

List Exact Matches In 2 Columns On 2 Sheets

Mar 14, 2008

Most likely another very basic on...

2 worksheets.
Worksheet 1, holding all data in column C.
Worksheet 2, the value in column B.

In need for a function that list all exact matches of the values in worksheet 2 column B that are found in Column C of worksheet 1.

I got this VBA code that checks for the string in worksheet 2, column B, but it is a Instr, and I need a exact match and only the exact match. Is this difficult to modify and how?

Or would this be a regular array function?

(I would not need a macro, since the data change constantly and I prefer function as they update easily)

Function matchArray(ByVal testString As String, ByVal dataRange As Range, Optional IndexNum As Long) As Variant
Dim outRRay() As String
Dim rangeArray As Variant
Dim xColl As New Collection
Dim xVal As Variant
Dim rIndex As Long

View 9 Replies View Related

Multiple Matches In Array (one Or Two Criteria) And List Results

Sep 15, 2014

I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:


I use it to go for work

[Code] .....

I want to be able to do following (2 separate tasks):

Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):


I use it to go for work

Family trips
Weekend fun
2nd car

Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):




I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.

View 2 Replies View Related

Conditional Format Dates In A Calender When Matches Dates In A List

May 14, 2009

Hi Guys, This has been bugging me for a bit now and I just can't sus it...

I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.

I have beside that a column for holidays, etc and then a another column for other events.

When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).

The formula I have been playing with (no success) is:

=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.

The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...

Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...

I have attached the spread sheet that I am working on.

View 2 Replies View Related

How To Return First Non-blank Cell That Matches Another Cell

Jul 24, 2013

I would like to return in column E if the person purchased another package before using all their product up. There are multiple accounts in column A that has a similar set. I need a formula that keeps the used date unique to the account # as well.

Account #


View 2 Replies View Related

Add "x" If Customer Number Matches A List

Jan 9, 2007

I have 1) a list of 10,000 customer records with customer numbers (including duplicates) in field A, and 2) a separate list of 25 customer numbers that are "red" accounts.

I want to add a new ID field to the larger customer list, and using the 25 red IDs, I want to mark any of the 10K records that match the list of 25 with an "x".

I thought about using multiple nested-function formulas and concatenating the results, but then, I always do things the hard way! ...

View 9 Replies View Related

Formula To Find Matches And Return Yes Or No

Jun 11, 2014

In the attached sample work book Col E has text that I want to check if it is also in Col G and return Yes or No into Col F

View 4 Replies View Related

Identify Matches And Return Values

Nov 10, 2009

1) Check to see if a value in column A of worksheet A appears in column A on worksheet B.

2) If it DOES, then display the value from the same row from column B on worksheet B, in column B on worksheet A.

3) If it DOESN'T, display the value "EACH" in column B on worksheet A.


View 2 Replies View Related

Search And Return Address For All Matches

Feb 14, 2010

I'm looking for a Formula in order to retrieve all addresses of all matches equal to the search criteria located in cell: E2

The results are hand-typed in col. G

This is a case of a TWO-DIMENSIONAL "Arbitrary Lookup" Arbitrary.

The range A1:C10 was named: TBL and the formula should refer to TBL as it can be dynamic in size.

PS: can anybody tell me why I don't get any E-Mails when an answer is posted?

View 13 Replies View Related

How To Return Exact Matches Only Using VLookup

Feb 13, 2007

I have the following formula which, if text is in A1, will lookup from a list of worker's comp codes to locate the code in A2 and return the wording for that

However, if there is not an exact match to the code in A2, it returns the wording for the next closest code.

Is there a way to make it so that it will only return the wording for the exact match and return "invalid code" if the number in A2 is not found on the list on the "Codes" sheet?

View 9 Replies View Related

Return Row Number That Matches 2 Criteria

Feb 15, 2010

I have a produced an Excel workbook which uses a VBA sign in/out userform.
Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.

To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8.
I need to have the row number returned for the IDnumber where the Out cell is blank.
This should be the last occurrance of the IDnumber

1 ID# In Out
2 1 9:00 13:00
3 4 9:00 13:00
4 3 9:00 13:00
5 2 9:00 13:00
6 5 9:00 13:00
7 6 9:00 13:00
8 2 14:00

View 5 Replies View Related

Compare 2 Columns And Return Matches

Aug 12, 2008

I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.

View 5 Replies View Related

Return Value If Number Is In Range Even And Then Matches The Street

Mar 16, 2014

Basically, I have a huge list of addresses with ID numbers, and a list without the ID numbers. An example would be;

2-10 (evens), test road, ID123.

I need to identify if the address is on the street, is in that block of flats (i.e between 2-10, and even number), and then return the ID.

I've already split the number between two cells, but then its all gone a bit wrong.

View 11 Replies View Related

Two Criteria - Multipule Matches That Are The Same - 1 Answer To Be Return

Oct 16, 2008

I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.

What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.

View 3 Replies View Related

Compare Column In Different Workbooks & Return Corresponding Value Of Matches

Aug 7, 2008

I have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:

Workbook 1: Macros.xls
Workbook 2: MASS 162.xls
The SSN column is column C in both workbooks
When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.

View 7 Replies View Related

Find Matches In Multiple Columns And Return Header

Oct 29, 2012

Lets see if I can put this into words.

I am trying to find matches of a specific cell in various columns. Example:

Header 1 -------Header 2----------Header 3

If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times

So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"

Is this possible?

View 2 Replies View Related

Lookup Function Required To Return Multiple Matches

Apr 24, 2007

I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...

My column headings are as follows...
Type Region Contact Phone Email ADD1 ADD2 ADD3

In column A (type) would be the type of contact eg:business/personal/family etc.

So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense!
There are over 1000 records i need excel to search through & return matches.

I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.

View 9 Replies View Related

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